Problem with the MOD function in excel?

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.

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

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.

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.

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.

=INT((6-2*2^-50)/6) returns 1 (exactly) in Excel 2010, and =6-2*2^-50 returns 6 (exactly).

In 07

 INT((6-2*2^-50)/6)

Returned a value of 1

HansV wrote:

=INT((6-2*2^-50)/6) returns 1 (exactly) in Excel 2010

Brad wrote:
> In 07 INT((6-2*2^-50)/6) Returned a value of 1

Thanks.  Too bad!

HansV wrote:
> =6-2*2^-50 returns 6 (exactly).

Are you sure?!  I would find that very surprising.

No doubt that the result appears to be 6 "exactly" even if formatted to display 15 significant digits.

But if A1 is =6-2*2^-50, what does =A1-6-0 in A2 return formatted as Scientific?

That is the most reliable way to "see" that it is not truly "6 exactly".

If A2 is truly exactly zero in that case(0.00E+00 !), what does A2 return if A1 is =6-2*2^-50-0?

Sigh, one of these days I need to get my own XL2010 so I don't have to burden people with these experiments.  Unfortunately, I don't believe I can get XL2007 anymore.

With =A1-6-0, we're back at -1.77636E-15. If must say that I find this behavior very confusing. What is the difference between =A1-6 and =A1-6-0?

HanV wrote:

With =A1-6-0, we're back at -1.77636E-15. If must say that I find this behavior very confusing. What is the difference between =A1-6 and =A1-6-0?

If you find it confusing, I'm sure my explanation did little to alleviate Jennie's confusion.  My apologies for getting too mired down in details.  To answer your question....

Refer to the poor description of the dubious heuristic under the misleading title "Example When a Value Reaches Zero" in http://support.microsoft.com/kb/78113 [3].

In short, sometimes Excel forces an arithmetic result to be exactly zero if it considers the result to be "close enough" to zero [1].

The operative word is "sometimes".  KB 78113 states that it is when "an addition or subtraction operation result in a value at or very close to zero".  But that is imprecise.

First, it applies to the last arithmetic operation of a formula, not to subexpressions [2].

Second, it applies when the last operation is subtraction of two operands with the same sign or addition of two operands with opposite signs.

Third, and this is the key:  it applies when the last operation changes the result.

Parentheses count as an "operation" in this context.  So =A1-6 is zero, but =(A1-6) is not zero, just as =A1-6-0 is not zero.  The last operation, right parenthesis or -0, does not change the result.

Don't try to make sense of that.  It does not, IMHO.  It is just the way it is.

Bottom line:  If you want to know the true value of an expression that might be close to zero, always append -0 to it.


-----
Endnotes

[1] Let's not get into the definition of "close enough".  To my knowledge, Microsoft never documents the criteria.  I have deduced some of my own; but they are too complicated to explain, and they might be incomplete.

[2] The "close to zero" heuristic also applies to comparisons of the form x=y, which we can think of x-y compared to zero.

Generally, I don't believe this heuristic applies to Excel functions.  But recently, I discovered that SUM has its own heuristic for changing some "close to zero" results to exactly zero.  Too difficult to explain.  Suffice it to say:  IMHO, it is even more fickle than the addition/subtraction rule in KB 78113.

[3] It is a "poor" description because it is incomplete and imprecise.

It is a "dubious" heuristic because it is not applied consistently, leading to many seemingly inexplicable conditions.  For example IF(A1=6,TRUE) is TRUE and =A1-6 is exactly zero, but IF(A1-6=0,TRUE) is FALSE.

It is a "misleading" title because the heuristic is not limited to results that are "close to zero".  For example, if A1 is =2^104+2^52, =A1-2^104 results in exactly zero, but =A1-2^104-0 demonstrates that the result is 2^52, which is obviously not "close to zero".