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

Whether you understand all the explanations or not, it is clear that a workaround should be used. You might use the INT function as explained before. However, I have another one, which you might like:

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

Answer 2

Koeiegeloei wrote:

Whether you understand all the explanations or not, it is clear that a workaround should be used. You might use the INT function as explained before. However, I have another one, which you might like:

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

Although that seems to work with 1.4-1, it does not work with some larger numbers that also should result in 0.4 mathematically, e.g. 1000.4-1000.

Again, the reason is due to inherent limitations of the internal 64-bit binary floating-point representation.  And again, the more reliable work-around is:

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

Change ROUND(...,2) to whatever precision is sufficient for your application, presumably much less than 15.

[ERRATA] Although MOD(ROUND(15*(1.4-1),2),6) is sufficient for the expression 15*(1.4-1), which should result in an integer mathematically, the following is more reliable in general, where A1 is any real number:

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

See the later response to Koeiegeloei for an example that demonstrates why the outer ROUND(...,2) is usually needed.

---

PS:  I bristle when I see conditional expressions like MOD(15*(1.4-1),6)=6.  Again, the expression MOD(15*(1.4-1),6) does not return exactly 6, even though Excel displays 6.00000000000000 (15 significant digits).  So MOD(15*(1.4-1),6)=6 returns TRUE, but MOD(15*(1.4-1),6)-6=0 returns FALSE(!).

The difference arises because of an arbitrary algorithm (not computer arithmetic) that is similar to the dubious "close to zero' heuristic vaguely described in http://support.microsoft.com/kb/78113.  In other words, Excel fakes the result of MOD(15*(1.4-1),6)=6.

Answer 3

  And again, the more reliable work-around is:

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

Thanks Joeu2004! You clearly pointed out the drawbacks of the if-construction.

However, your workaround is not 100% reliable either, unfortunately.

I tried to substitute your proposal in in one of my worksheets and found the following anomaly, which I reproduce in a simplified version below:

Formula	Result
0	0
=(MOD(ROUND(A1+1/3,12),1))	0.333333
=(MOD(ROUND(A2+1/3,12),1))	0.666667
=(MOD(ROUND(A3+1/3,12),1))	1

The last result should of course be 0. Rounding to less or more digits does not change the result... (By the way, the IF construction that I proposed, gives the same erroneous result.)

Now, I guess that most people need a work-around that is 100% reliable. So far, it seems to me that there is no such workaround.

Answer 4

Thanks Joe for the detailed explanation. Interesting but surprising and worrisome reading.

It is just a thought, not a statement, but should we use something like MOD when one of the arguments is not an integer (integer in the mathematical understanding!)?

At my age, the school years are far away, we learned to devide integers and return the remainder. So my question is, should we do this with real numbers? Mathematically seen, there are a few integers but there are googols^googols (and even googolplex^googolplex) of reals.

Knowing that most real numbers are infinite and even not have a repeating sequence, does it make sense to calculate the remainder of real numbers? In most cases the quotient multiplied by the devisor + the remainder will not return the original value. That's why I doubt.

Answer 5

JP wrote:

should we use something like MOD when one of the arguments is not an integer

Knuth seems to think so.  He writes [1]:

``If x and y are any real numbers [...]:

    x mod y = x - y*floor(x/y), if y<>0

[....] The "mod" operation is also useful when x and y take arbitrary real values; for example [...]:

   tan x = tan (x mod pi)``

That works because mathematical arithmetic has infinite precision.  However, computer arithmetic has finite precision.  So in retrospect, I should have written:

ROUND(MOD(ROUND(15*(A1-1),2),6),2)

A1=1.4 in the Jennie's example.  I presume it might be any real value (integer or non-integer).

Change ROUND(...,2) to whatever precision is sufficient for your application, presumably much less than 15.  Jennie never described the application (context of the computation).

JP wrote:

Mathematically seen, there are a few integers but there are googols^googols (and even googolplex^googolplex) of reals.

Mathematically, there are an infinite number of integers and an infinite number of non-integers.

In computer arithmetic that relies on normalized 64-bit binary floating-point (e.g. Excel), the number of non-negative real-number representations (integers and non-integers) is 2^2098, which is less than 10^632 or googol^7.

---

[1] Knuth, Donald, "The Art of Computer Programming, Volume 1".