"ak_edm" wrote:
The formula =(1887.36-314.56)/314.56 equals 5
but the formula =MOD(1887.36-314.56,314.56) equals -5.68434E-14
[....] Why?

Because most decimal fractions cannot be represented exactly in the binary
form that Excel uses (IEEE 64-bit floating-point). This introduces
numerical abberations in many calculations. I will explain further below.


How do I fix it?

=ROUND(MOD(1887.36-314.56,314.56),2)

Change 2 to whatever precision you want -- up to 12 with these particular
numbers. (Caveat: 12 might not work with other numbers, though.)


The problem is: 1887.36 - 314.56*5 does not exactly equal 314.56 when you
look at the internal representaion

314.46 is represented internally exactly as
314.560000000000,00227373675443232059478759765625. (The comma is my way of
demarcating 15 significant digits to the left.)

The result of 1887.36 - 314.56*5 is represented internally exactly as
314.559999999999,94543031789362430572509765625.

Note that both will appear as 314.560...0 when displayed with 15 significant
digits, the most that Excel will format.


----- original posting -----

"ak_edm" wrote in message
...
I have Excel 2003 (11.8237.8221) SP3 according to the ABOUT screen in the
HELP menu. The three numbers and their cell locations a

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