Problem with the MOD function in excel?

Question

=MOD((15*(1,4-1));6)

returns 6,0 and

=MOD((15*(0,4));6)

returns 0,0

I would like the first MOD function to return 0 as well. Is there a reason why Excel behaves like this? Do you experience the same problem in excel 2010?

I could replace the function with a INT-test instead ( =(15*(1,4-1))-6*INT((15*(1,4-1))/6) ), but it would be nicer if MOD would work.

Office 2010 - I did not experience the same problem in office 2003

Thanks

Answer 1

Jennie wrote:

=MOD((15*(1,4-1));6)

returns 6,0 and

=MOD((15*(0,4));6)

returns 0,0

I would like the first MOD function to return 0 as well. Is there a reason why Excel behaves like this?

Yes.  It is explained in overwhelming detail in http://support.microsoft.com/kb/78113.

In a nutshell, the problem is due to the fact that Excel (and most applications) uses binary floating-point to represent numbers.  Consequently, most non-integers (and integers greater than 2^53) cannot be represented exactly.  The usually-infinitesimal differences sometimes become noticable after some arithmetic operations.

In your case, 1.4 is represented as 1.39999999999999,9911182158029987476766109466552734375, so 1.4-1 is represented as 0.399999999999999,911182158029987476766109466552734375.  The result of multiplying by 15 is represented as 5.99999999999999,82236431605997495353221893310546875.

Note:  Because Excel displays only the first 15 significant digits, rounding the 16th, these intermediate results will display as 1.40000000000000, 0.40000000000000 and 6.00000000000000.  But they really aren't.  You can verify this by entering, for example, =A2-0.4-0 formatted as General, where A2 contains =1.4-1.  The redundant -0 is needed to avoid the dubious heuristic poorly described under the misleading title "Example When a Value Reaches Zero" in KB 78113.  In short, sometimes Excel forces an arithmetic result to be exactly zero if it thinks the result is "close enough" to zero.

In contrast, the constant 0.4 is represented by 0.400000000000000,02220446049250313080847263336181640625.  By coincidence, multipying by 15 results in exactly 6.  But I empahsize:  that is only by coincidence.

The general solution is to explicitly round expressions to the precision that your require.  For example:

=MOD(ROUND(15*(1.4-1),2),6)

if you required accuracy to 2 decimal places.

Jennie wrote:

> I did not experience the same problem in office 2003

I am using XL2003 for all of my work above.  So yes, the same problem exists in XL2003.

You might not have seen it in XL2003 because instead of those exact constants, you were working with the results of other arithmetic operations which only appeared to have results like those constants.  Alternatively, you might have been working with very different constants.  Each arithmetic situation is a little different.  It is very difficult to predict which combinations of numbers will exhibit these floating-point anomalies.

Answer 2

This does appear to be a bug - probably the result of the "improved accuracy" of the MOD function in Excel 2010... :-(

Answer 3

HansV wrote:

This does appear to be a bug - probably the result of the "improved accuracy" of the MOD function in Excel 2010... :-(

Not exactly.  As I explained, XL2003 also fails to return zero for Jennie's example using constants.

And it is not a "bug".

However, when I said "the same problem exists in XL2003", I should have been clearer:  the same problem, but different non-zero results.

(And sometimes those "different results" might meet expectations by coincidence.  But I emphasize:  by coincidence.)

XL2003 MOD returns about -1.776E-15.  I don't know why exactly.  (Although I can speculate.)

XL2010 MOD returns about 6.  I suspect it is exactly 5.99999999999999,82236431605997495353221893310546875.  Try computing =A1-6-0, where A1 is =MOD(15*(1.4-1),6).  I suspect it is about -1.776E-15.

Indeed, that is what a VBA implementation of the MOD formula returns, especially if we emulate the way that Excel converts pairwise arithmetic operations to 64-bit, but also even if we rely on VBA's ability to retain intermediate results in 80-bit floating-point.

Compare the results of the following VBA functions, always remembering to use the =A1-6-0

paradigm; that is, the redundant -0.

As I said, this is not a defect.  Note that 6 (approximately) is indeed the correct result of MOD(15*(1.4-1),6), given the fact that 15*(1.4-1) is less than 6.  Int(5.9999999999999982236431605997495353221893310546875/6) should be zero [*].  Ergo, x-6*Int(x/6) should be x, which is 5.9999999999999982236431605997495353221893310546875.

VBA functions.....

Function myMOD1() As Double

'emulate Excel computation, converting

'pairwise operations to 64-bit floating-point

myMOD1 = 1.4 - 1

myMOD1 = 15 * myMOD1

myMOD1 = myMOD1 - 6 * Int(myMOD1 / 6)

End Function

Function myMOD2() As Double

'VBA retains intermediate results in 80-bit

'floating-point registers when possible

myMOD2 = 15 * (1.4 - 1) - 6 * Int(15 * (1.4 - 1) / 6)

End Function

---

Endnotes

[*] I need to clarify this.  But my wife is pushing me out of the house.  Back in about 30 minutes.

Answer 4

Thanks for the explanation. You're correct that with the formula =MOD(15*(1.4-1),6) in A1, the formula =A1-6-0 returns -1.77636E-15 in Excel 2010 too.

Answer 5

HansV wrote:

Thanks for the explanation. You're correct that with the formula =MOD(15*(1.4-1),6) in A1, the formula =A1-6-0 returns -1.77636E-15 in Excel 2010 too.

Thanks for the confirmation.

I wrote:

> 6 (approximately) is indeed the correct result of MOD(15*(1.4-1),6),

> given the fact that 15*(1.4-1) is less than 6.

> Int(5.9999999999999982236431605997495353221893310546875/6)

> should be zero [*].  Ergo, x-6*Int(x/6) should be x, which is

> 5.9999999999999982236431605997495353221893310546875

Excel has so many floating-point anomalies, some of which are purposeful, it is difficult to make any statement of fact without a lot of ifs-ands-or-buts qualifiers.

If you type =INT(5.9999999999999982236431605997495353221893310546875/6), it does indeed return zero.  But that is because Excel truncates constants to 15 significant digits.  So we are effectively entering =INT(5.99999999999999/6).

However, if A1 contains a formula that results in exactly 5.9999999999999982236431605997495353221893310546875 [*], you might find that =INT(A1/6) is 1.  At least that is the case in XL2003.

That might be what you expect if you display A1 to 15 significant digits.  But in fact, that is a defect, IMHO.

Since the value in A1 is less than 6, INT(A1/6) should be zero.  But as I have explained in the past , Excel INT (in XL2003) apparently rounds to 15 significant digits before truncating.  INT(5.999999999999998.../6) is about 0.999999999999999,6669.  That is rounded first to 1.00000000000000, which obviously truncates to 1.

I suspect that explains why MOD(15*(1.4-1),6) returns about -1.776E-15 (almost zero) in XL2003.  I suspect that XL2003 MOD is implemented by computing x-6*XLint(x/6), where XLint is effectively the Excel INT function.  Note that =15*(1.4-1)-6-0 is about -1.776E-15.

Apparently, XL2010 MOD is implemented by computing x-6*TrueInt(x/6), where TrueInt can be represented by the following VB implementation:

Function TrueInt(x) As Double

TrueInt = Int(x)

End Function

Note that =15*(1.4-1)-0 in A2 is about 5.999999999999998, which we can determine by entering =A2-6-0 formatted as General.

PS:  I don't know if the implementation of XL2010 MOD was changed in this manner, or if XL2010 INT was (also) changed.  What does INT((6-2*2^-50)/6) return in XL2010?  In XL2007?

---

Endnotes

[*] "Exactly 5.9999999999999982236431605997495353221893310546875".  For example, =6-2*2^-50.