Errata^2....

I wrote:
I misled myself due to a mistake. Coincidentally, when I computed
MOD(1887.36 - 314.56*5,314.56), I got exactly the same result as
your MOD expression yields.

I meant: when I computed (1887.36 - 314.56*5) - 314.56.

I better quit while I'm behind :-).


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

"JoeU2004" wrote in message
...
Errata....

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

But you might ask: "What does that have to do with the price of tea in
China?" :-)

I misled myself due to a mistake. Coincidentally, when I computed
MOD(1887.36 - 314.56*5,314.56), I got exactly the same result as your MOD
expression yields.

But I -- and MOD -- should be computing (1887.36 - 314.56) - 314.56*5.
And that does exactly equal zero.

If I reorder terms -- namely, =(1887.36 - 314.56*5 - 314.56) -- my
previous explanation would explain the MOD result. But there is no reason
to think that MOD reorders those terms. It should see only the result of
1887.36 - 314.56, not the individual operands.

Since =(1887.36 - 314.56)/314.56 does equal exactly 5 in the 64-bit
representation, I suspect the answer lies in the way that the MOD uses the
Intel-compatible FP instructions; namely, the fact that arithmetic can be
preformed in 80-bit floating-point registers.


"JoeU2004" wrote in message
...
"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