Beating a dead horse....
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.
Actually, the issue is not with the division [1], but with the precision
used for the subtraction in a - b*int(a/b).
I believe the following VBA "simulations" demontrate that. I call them
"simulations" because I do not really know how the Excel MOD function is
implemented.
The following function has the same non-zero result as =MOD((1887.36 -
314.56),314.56).
Function mymod(a As Double, b As Double) As Double
Dim x As Double
x = a / b
mymod = a - b * Int(x)
End Function
The reason is because the expression a - b*Int(x) is evaluated in 80-bit FPU
registers, and the 80-bit intermediate result for b*Int(x) (314.56*5) has
bits beyond the first 64 bits [2].
This is confirmed with the following function, which computes a - b*Int(x)
as Excel would, putting intermediate results into 64-bit registers. The
result of the function is zero.
Function mymod2(a As Double, b As Double) As Double
Dim x As Double, y As Double
x = a / b
y = b * Int(x)
mymod2 = a - y
End Function
-----
Endnotes:
[1] Actually, there is a potential issue with the precision of the
(1887.36 - 314.56)/314.56 itself. But I work around it in the macros by
storing the result in a type Double variable. Presumably the Excel MOD
function does that, too, or it works around the issue another way, perhaps
even by a different algorithm. Otherwise, MOD() would return a completely
incorrect answer, namely about 314.56, it if used the most straight-forward
implementation of a - b*int(a/b).
[2] Recall that 314.56 cannot be represented exactly. Its binary
representation is &h4073A8F5,C28F5C29. That is my stylized form of the
64-bit FP value in hex, &hEEEM...M, where "E" is the biased exponent and "M"
is the mantissa.
-----original posting -----
JoeU2004 said:
Errata....
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 said:
ak_edm said:
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.
=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 said:
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