While you are correct that this is a direct result of the limitations of IEEE
standard double precision (common to almost all general purpose software),
and the change in 2003 to a 2-pass algorithm for STDEV, VAR, etc is a welcome
numerical improvement, there is still room for improvement in Excel's
algorithms here. If Excel used updating algorithms
http://groups.google.com/group/micro...6ee0c636ad016a
for AVERAGE, STDEV, COVAR, etc, then STDEV would always return zero for
constant data, GEOMEAN would never overflow unless individual observations
overflowed, ...

Jerry

"JE McGimpsey" wrote:

No, STDEV "works" for fractional as well as whole numbers, but you are
seeing the limitation of IEEE Double Precision Floating Point math,
which is used by XL and most other spreadsheets. There's no patch,
because XL can't determine whether the small rounding error is real or
an artifact.

Nearly all numbers cannot be exactly represented in a finite number of
binary digits, just as the number 1/3 cannot be represented in a finite
number of decimal digits. So you should expect small errors due to
rounding. You can use ROUND() to filter out these errors, e.g.,

=ROUND(STDEV(A2:A61),9)

See

http://cpearson.com/excel/rounding.aspx

for more details



In article ,
Jacky wrote:

Is this due to limitation of the excel formula (STDEV only works for whole
numbers)? Or is there any patch for this error?