Excel formula does not work

  • Thread starter Thread starter Tiffany
  • Start date Start date
T

Tiffany

(18,201.800000000000000000) +
18,201.970000000000000000 = 0.169999999998254

HELP!!!

I have precision as displayed set and it does not help.
I specifically upgraded to XP because there was no support
for this problem in 95.

Tiff
 
Then I'm afraid you wasted your money (though you undoubtedly got a lot
of nice new features).

This is a problem with *every* spreadsheet that uses finite precision to
do math (which all of them do).

In the same way that 1/3 (along with most other numbers) cannot be
exactly represented in decimal notation, since we have to stop writing
3's in 0.3333333... at some point, so too in binary, most numbers are
not able to be represented exactly. XL has 15 decimal digits of
precision, and uses IEEE double precision floating point math routines
to try to minimize any rounding error. But some rounding error will
always exist.

One fix:

=ROUND(-18201.8 + 18201.97, 5)

=0.17000


another is to check the Precision as displayed checkbox in
tools/Options/Calculate. This is a global setting, however, and will
affect all the calculations in your workbook.

For more, see http://cpearson.com/excel/rounding.htm
 
Back
Top