ExcelBanter - View Single Post

wrote...
I wrote:
I am mystified by the fact that this numerical error
is not evident when I program #1 in VBA, which does
display var = 0 -- a pleasant surprise. Without access
to the VBA binary representations, VBA compiled code
and internal Excel algorithms, I can only speculate wildly
about the disparity.

My suspicion was confirmed by a response by Martin
Brown to my inquiry in excel.programming and by some
more experimentation on my part. Apparently, even the
retooled Excel 2003 STDEV() implementation does not
take full advantage of the floating point coprocessor, as
the VBA compiler seems to. I'm surprised.

It's not the STDEV function, per se, it's the mean. The mean of x, x
and x isn't exactly x. That is,

=SUMSQ(1.4434-AVERAGE(1.4434,1.4434,1.4434),
1.4434-AVERAGE(1.4434,1.4434,1.4434),
1.4434-AVERAGE(1.4434,1.4434,1.4434))

isn't zero. This even defeats DEVSQ, which is usually pretty good, but

=DEVSQ(1.4434,1.4434,1.4434)

returns 1.47911E-31 rather than zero. More to the point, while the
formula

=1.4434-AVERAGE(1.4434,1.4434,1.4434)

returns zero, the formula

=(1.4434-AVERAGE(1.4434,1.4434,1.4434))

returns 2.22045E-16.

Ain't Excel fun!

As with all floating point calculations, use explicit rounding for
final results. In this case,

=ROUND(STDEV(1.4434,1.4434,1.4434),5)

where the 5 is the maximum significant digits in STDEV's arguments.