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

Koeiegeloei wrote:

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.

Your example reflects a number of misunderstandings.

I wrote:  "Change ROUND(...,2) to whatever precision is sufficient for your application".

When we round to a specific precision, the presumption is that either we format the results with at least the same number of decimal places, or we understand the difference between displayed and actual values.

If we format the results with 12 decimal place, consistent with your use of ROUND(....,12), we see the following:

The formulas in column A are displayed in column B.

Hopefully, it is clear now that the result in A4 is indeed correct.

It only appears to be 1 because you formatted with less than 12 decimal places.  Actually, it appears that you used the General format.  So Excel decides the number of displayed decimal places arbitrarily.

When we display fewer decimal places, Excel rounds the displayed value.  But usually [1], that does not change the underlying actual value.

So A2 is actually 0.333333333333 (12 digits).  When you add 1/3 in A3, the result is 0.666666666666333 (15 digits), which you round to 0.666666666666 (12 digits).  Similarly, when you add 1/3 in A4, the result is 0.999999999999333, which you round to 0.999999999999.

Koeiegeloei wrote:

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.

As I wrote in my response to JP, the more reliable solution is, in general:

ROUND(MOD(ROUND(A1+1/3,12),1),12)

The outer ROUND(...,12) is not needed for your examples because A+1/3 is less than 1 in all cases.  But consider the following example:

Column A is formatted as Number with 15 decimal places so we can see the problem.

A6 is correctly 100.333333333333 (12 decimal places).  When we add 1/3 and round, A7 is correct 100.666666666666.  But in A8, MOD(A7,1) is 0.666666666666003 (15 decimal places).  As demonstrated in A9, we must explicitly round in order to get the correct result to the desired precision of 12 decimal places.

---

The reason why A8 is 0.666666666666003 is complicated.  It really is not necessary that you understand the explanation.

The reason is that the number in A7 is represented by a sum of 53 consecutive powers of 2 ("bits") times an exponential factor.  7 of those bits represent the integer 100.  The remaining 46 bits are the best approximation of the fraction 0.666666666666.

When we do MOD(A7,1) in A8, we simply strip away the integer part.  Thus, 0.666666666666 is still represented using only 46 bits.  The remaining 7 bits to the right are zero.

But the constant 0.666666666666 can be represented more accurately using all 53 bits:  as many as 7 more powers of 2, which are increasingly smaller fractions in this case.

This might be clearer if you could see the exact internal values, not limited by the 15 significant digits that Excel will format.  (In the following, period (".") is the decimal point, and comma (",") demarcates the first 15 significant digits.)

A7:  100.666666666666    100.666666666666,00349344662390649318695068359375

A8:  =MOD(A7,1)                0.666666666666003,49344662390649318695068359375

A9:  0.666666666666           0.666666666666000,05175524756850791163742542266845703125

As you can see, the difference between A7 and A8 is simply that 100 is subtracted, effectively shifting the first 15 significant digits left to include the 003 that was there all along.

But the constant 0.666666666666 is represented more accurately with 000 following the first 12 significant digits, in this case.

---

[1] The format affects the underlying actual value only when the "Precision as displayed" option is set.  But in that case, you really should format with 12 decimal places, since ROUND(...,12) implies that that is the precision you require.  Note:  I do not recommend that you use "Precision as displayed".

Answer 2

Joeu2004 wrote:

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

I know of course that there are an infinite number of integers and non-integers. What I would say is that the chance that a calculation returns an integer is less than the chance to get a non-integer back.

Thanks again for the feedback.

Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)

Answer 3

Hello Joeu

Any chance you could point me to the place where I can learn how to show, for example,

A7:  100.666666666666    100.666666666666,00349344662390649318695068359375

I did have a macro from Jerry Lewis at one tine

best wishes

Bernard

bliengme - at - stfx - ca

Answer 4

Hello Joeu

Any chance you could point me to the place where I can learn how to show, for example,

A7:  100.666666666666    100.666666666666,00349344662390649318695068359375

I did have a macro from Jerry Lewis at one tine

best wishes

Bernard

bliengme - at - stfx - ca

Hi Bernard. 

I don't have a "full" solution for you, and I'm sure you'll get a better response shortly.

For now,  here's a quick n dirty way to show the fractional values of the number you mentioned.

This is a demo of the fractional part, and not a complete solution:

Sub Demo()

    Dim x, y, j, k, s

    Dim n, d, t

    n = 0 'Numerator

    d = 1 'Denominator

    t = 2

    s = vbNullString

    x = 100.666666666666

    For j = 8 To 53

        y = x * 2

        k = Int(y) Mod 2

        s = s & CStr(k)

        x = y - Int(y)

        If k = 1 Then

            n = 1 + n * t / d

            d = t

        End If

        t = t * 2

    Next j

    Debug.Print s

    Debug.Print "Num: ", n

    Debug.Print "Den: ", d

End Sub

Returns:

1010101010101010101010101010101010101001111100

Num:           11728124029599

Den:            17592186044416

Now, the 100 is the 7 digit binary number (not calculated above).    But, the binary digit string is the fractional part.  The first three values are 101... which means 1/2 + (Skip 1/4) + 1/8. ...etc   (We skip the zeros in the addition.   

So, at full precision with the above we get  11728124029599  / 17592186044416

11728124029599 / 17592186044416

0.66666666666600349344662390649318695068359375000000000

For the op, who asked... "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? "

That value is just the 49th bit that got thru.

=Power(2, -49)

1.77635683940025E-15

For a problem with known accuracy of inputs (say out to 1 or 2 decimal places), I would work with integers and vba.

HTH  :>)

Dana DeLouis

Answer 5

Koeiegeloei wrote:

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

I wrote later in response to JP:

JP wrote:

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

[....]

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)

I wanted to return to these points.

First, as I noted, in general, I believe ROUND(MOD(A1,A2),2) is better than MOD(A1,A2) when one or both of A1 and A2 are non-integers, and we expect the result to be accurate to a specific number of decimal places (2 in my examples).

Second, in that case, I believe Koeiegeloei's formula is indeed more reliable, when corrected.  Assuming A1 and A2 are already rounded to the required precision, use:

IF(ROUND(MOD(A1,A2),2) = A2, 0, ROUND(MOD(A1,A2),2))

This is needed because even though MOD(A1,A2) - A2 < 0 is always true [1], ROUND(MOD(A1,A2),2) = A2 might become true due to rounding.

Third, JP is correct to be concerned about MOD with non-integers, due to the limitations of computer arithmetic [2].  But I believe Koeiegeloei's formula, as corrected above, ameliorates those concerns.

---

[1] We need to write MOD(A1,A2) - A2 < 0 in order to discover that MOD(A1,A2) < A2 is really true.  Excel sometimes treats MOD(A1,A2) < A2 as true arbitrarily, in a manner similar to the dubious "close to zero" arbitrary adjustment discussed in http://support.microsoft.com/kb/78113.

[2] All computer arithmetic has finite limitations, whether it uses binary or decimal representation.  But some algorithms can mitigate the deleterious effect of those finite limitations in some cases.