Excel 2003 Calculation Problem

**akedm** · 04-03-2009, 03:05 PM

I have Excel 2003 (11.8237.8221) SP3 according to the ABOUT screen in the
HELP menu. The three numbers and their cell locations are:

F17 = 1887.36
F18 = 314.56
F21 = 314.56

Note that 314.56 x 6 = 1887.36, so why is this happening?

The formula =(1887.36-314.56)/314.56 equals 5
but the formula =MOD(1887.36-314.56,314.56) equals -5.68434E-14

(for this next one I formatted the digits in the Excel cell as far as I
thought good)
The formula =(F17-F18)/F21 equals 5.000000000000000
but the formula =MOD(F17-F18,F21) equals 0.000000000000171

Why?
How do I fix it?

- Eric

**shg** · 04-03-2009, 03:09 PM

You only get about 15 digits of precision in a double-precision floating point number. It's a limitation of the way computers store real numbers.

**akedm** · 04-03-2009, 04:04 PM

That's right.

But I shouldn't need 15-digits of precision. I shouldn't need one. It should calculate to a whole number without decimal places. Please re-create this on your own Excel 2003 (or whatever version you have). Any ideas?

**ChemistB** · 04-03-2009, 04:10 PM

The point is that computers use binary code and calculate using binary numbers. We use base 10 and sometimes there are slight differences between them as there is not always a real number equivalent between them. The results to your formula should be 0.0 but when performed in binary, you get that discrepancy at the 15th,16th place. The only way to get around that is to use the ROUND function.