Re: Rounding error in Stdev function result.
- From: "Harlan Grove" <hrlngrv@xxxxxxx>
- Date: 10 Jan 2006 13:17:45 -0800
joeu2004@xxxxxxxxxxx wrote...
....
>I understand. But the implementation of STDEV() could
>easily compute the mean internally, taking advantage of
>the 80-bit FP registers. It does not. Nor does AVERAGE(),
>as you point out. But the OP asked about STDEV(), not
>AVERAGE(). I tailored my comments to the OP's context.
80 bits wouldn't necessarily help in this case. If the mean is
calculated by summing all the values first then dividing by the count,
the sum itself could cause problems that dividing by the count wouldn't
necessarily solve. In an ideal world, cleverness would also be applied
to calculating the mean.
k = 0
n = 0
For Each v In d
n = n + 1
If n = 1 Then m = v Else m = m * (k / n) + v / n
k = n
Next v
Enter 1.4434 in A1:A3. Enter 1 in B1, 0 in C1 and =A1 in D1. Enter
=B1+1 in B2, =B1 in C2, =D1*(C2/B2)+A2/B2 in D2. Select B2:D2 and fill
down into B3:D3. Cell D3 contains the mean. Enter =AVERAGE(A1:A3) in
D4, and =(D3-D4) in D5. D5 evaluates to -2.22045E-16.
Enter =SUMSQ(A1-D4,A2-D4,A3-D4) in D6. It evaluates to 1.47911E-31. Now
enter =SUMSQ(A1-D3,A2-D3,A3-D3) in D7. It evaluates to 0.
Imagine that! Rounding error elimiated without using 80-bit FPU
registers!
Further, even calculating the mean as =(1.4434/3+1.4434/3+1.4434/3)
would eliminate the rounding error. Try
=SUMPRODUCT((A1:A3-SUMPRODUCT(A1:A3/3))^2).
The error is due *EXCLUSIVELY* to rounding error in the calculation of
the mean; however, it's possible to eliminate the rounding error
without using more bits of precision. For completeness, it's necessary
to understand that the order of operations is *ESSENTIAL* in floating
point arithmetic. While floating point addition and multiplication are
commutative, neither is associative, and the distributive law doesn't
hold (that is, all the time; there are fortuitous exceptions). In
floating point arithmetic it's usually best to perform multiplication
first, then addition if accuracy is more important than execution speed.
.
- References:
- Re: Rounding error in Stdev function result.
- From: Harlan Grove
- Re: Rounding error in Stdev function result.
- Prev by Date: adding/subtracting time
- Next by Date: Re: Recorded Macro to Copy Format
- Previous by thread: Re: Rounding error in Stdev function result.
- Next by thread: Re: Rounding error in Stdev function result.
- Index(es):
Relevant Pages
|
