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

Strange. This isn't a solution, but I did find that
=MOD(314.56*6-314.56,314.56)
also returns 1.7053E-13 (answer to one of your equations)

So, afraid I don't have any clue as to why XL is messing up, but wanted to
get an idea as to why the difference in 1.7053E-13 vs -5.68434E-14

For that matter, since when does division of a positve number by a postive
number yield a negative number?!?



--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ak_edm" wrote:

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

How do I fix it?


You don't it's not broken

The modulus is what's left over after division so

=MOD(F17-F18,F21)

should return zero compared to

=(F17-F18)/F21 which returns 5

The formula

=MOD(F17-F18,F21) if set to lots of decimal places returns

-0.0000000000000568434

which is near as dammit zero with the diference being caused by excel
conversions between binary and decimal where often there is no precise binary
representation of a number.

Mike

"ak_edm" wrote:

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

I missed answering the question

=ROUND(MOD(F17-F18,F21),1)

Mike

"Mike H" wrote:

How do I fix it?


You don't it's not broken

The modulus is what's left over after division so

=MOD(F17-F18,F21)

should return zero compared to

=(F17-F18)/F21 which returns 5

The formula

=MOD(F17-F18,F21) if set to lots of decimal places returns

-0.0000000000000568434

which is near as dammit zero with the diference being caused by excel
conversions between binary and decimal where often there is no precise binary
representation of a number.

Mike

"ak_edm" wrote:

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

"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

Luke,

For that matter, since when does division of a positve number by a postive
number yield a negative number?!?

It's all about precision and there being no precise binary representation of
the numbers your using. In the case of your numbers you need ~12 decimal
places before we get .0000000000001705.

Mike

"Luke M" wrote:

Strange. This isn't a solution, but I did find that
=MOD(314.56*6-314.56,314.56)
also returns 1.7053E-13 (answer to one of your equations)

So, afraid I don't have any clue as to why XL is messing up, but wanted to
get an idea as to why the difference in 1.7053E-13 vs -5.68434E-14

For that matter, since when does division of a positve number by a postive
number yield a negative number?!?



--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ak_edm" wrote:

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

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

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

Hi,

Thank you. I'm using Mike's ROUND function idea to fix my problems, which
came about when IF functions that replied on the MOD functions wouldn't work.
Thanks Joe to explaining it visually to me.

- Eric

"JoeU2004" wrote:

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

Beating a dead horse....

I wrote:
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" 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