MOD in Excel 2010 appears faulty. Different results b/n 2003 and 2010

[PastaPaul]

Hi All,

I'm hoping someone can look this over because I feel like I'm going nuts.

To the best of my knowledge the MOD function is expected to work the same in 2003 and 2010, but it doesn't.
I've created exactly the same test sheet in 2003 and 2010 and I'm getting different results.

=MOD(1.5,0.05) in Excel 2010 gives me a result of 0.05, but the same formula in 2003 gives me 0. Moreover, the correct answer to "what's the remainder of 1.5/.05" is 0, which means (as best as I can tell) 2010 is wrong!

The image I've attached is screen shots I've pasted together showing excel 2003 and 2010 giving different results to the same MOD function. What's even more weird is that 2003 and 2010 agree at 1.6 that the answer is 0 (go figure?!)
I've attached the test sheet as well. FYI (If anyone saves it in 2010 and opens it 2003 it will give the 2010 results. You need to F2 on the cells and press enter to see the 2003 answer. F9 will not recalc).

Can anyone confirm this and tell me if
a) I'm nuts or
b) There's a fix or
c) if MOD in 2010 is simply broken.


Thanks.

Attachment 443852

[jewelsharma]

such behavior maybe owing to Excel using binary floating point to represent numbers. However, it shouldn't be any different between the two versions. Please check this link. HTH!

[Tony Valko]

Different results b/n 2003 and 2010

What does "b/n" mean?

[PastaPaul]

shorthand for between

[PastaPaul]

Thanks for the reply.
The link does help explain what it's doing, but for the record it's definitely giving different results between the 2 versions. I also checked Excel 2007 which gives the same (correct) answers as 2003.

I've tried all sorts of combinations with the round function, but no combination will get the MOD function in XL 2010 to say the answer is 0.

After a bit more tinkering, it turns out my best option (seems so far to work in both versions) is ...

=ROUND(MOD(1.5,0.0499999999999999),2)

I'm a little nervous about it, but so far so good.

Paul

[joeu2004]

=MOD(1.5,0.05) in Excel 2010 gives me a result of 0.05, but the same formula in 2003 gives me 0.

As your screen shot demonstrates, it is not exactly zero in Excel 2003. It only appears to be zero because of your cell format.

If you format the cell as Scientific, you will see that it is about -8.32667E-17, as the screen shot shows directly under the MOD parameters.

(More precisely, -0.0000000000000000832667268468867,405317723751068115234375, which can be created with the formula =-8.32667268468867E-17-"4.1E-32".)

I use period as the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel formats, an arbitrary limitation.

The screen shot also shows "Function result = 0.0000000000". Apparently that shows the result as it appears in the cell.

Likewise, I believe that Excel 2010 does not return exactly 0.05, but about 0.0499999999999999 instead.

(More precisely, 0.0499999999999999,1950883071467615081928670406341552734375, which can be created with the formula =0.0499999999999999+"2E-17".)

I've tried all sorts of combinations with the round function, but no combination will get the MOD function in XL 2010 to say the answer is 0. After a bit more tinkering, it turns out my best option [...] is ...
=ROUND(MOD(1.5,0.0499999999999999),2)

I'm sure that will not work as intended with some combination of values.

I think it is better to use integer arithmetic. Conceptually, if you want the result to be accurate to 2 decimal places:

=MOD(150,5)/100
or
=ROUND(MOD(150,5)/100,2)

More generally, if A1 is 1.5 and B1 is 0.05:

=ROUND(MOD(ROUND(A1*100,0),ROUND(B1*100,0))/100,2)

The link does help explain what it's doing, but for the record it's definitely giving different results between the 2 versions. I also checked Excel 2007 which gives the same (correct) answers as 2003.

Yes; and that is what the comments say in the discussion that jewelsharma points to ("the link").

HansV explained that the difference arises as a result of changes in Excel 2010 to "improve accuracy". MOD is one of several functions that were "improved", IIRC.

I speculated that, in part, the difference might be 64-bit v. 80-bit binary floating-point arithmetic. And that does seem to be sufficient to explain the Excel 2010 result for =MOD(1.5,0.05).

I also speculated that, in part, the difference might be what I call "Excel INT" v. "true Int". That alludes to the fact that Excel INT does not always return the truly truncated integer result.

However, neither speculation explains the infinitesimal result from Excel 2003/2007 =MOD(1.5,0.05).

Well, I'm too tired to think straight.

Frankly, we might never be able to duplicate the internal calculation for Excel 2003/2007. Of course, it is not implemented in VBA or with Excel formulas.

[PastaPaul]

Thanks for this informative and well considered reply.

Your statement

I think it is better to use integer arithmetic. Conceptually, if you want the result to be accurate to 2 decimal places:

=MOD(150,5)/100
or
=ROUND(MOD(150,5)/100,2)

More generally, if A1 is 1.5 and B1 is 0.05:

=ROUND(MOD(ROUND(A1*100,0),ROUND(B1*100,0))/100,2)

is absolutely perfect. If nothing else it feels more comfortable because as you said it's working to a base 10 integer logic rather than a binary logic.

----

Just had an idea for a story (spoof) ... Some super computer becomes self aware. It determines humans need to be eradicated and (because of some quirk with binary "accuracy") it makes a bomb with helium instead of hydrogen. Everyone is running around in a panic about nothing and sounding like chipmunks!

[joeu2004]

Dispositive explanation....

I speculated that, in part, the difference might be 64-bit v. 80-bit binary floating-point arithmetic. And that does seem to be sufficient to explain the Excel 2010 result for =MOD(1.5,0.05).
I also speculated that, in part, the difference might be what I call "Excel INT" v. "true Int". That alludes to the fact that Excel INT does not always return the truly truncated integer result.
However, neither speculation explains the infinitesimal result from Excel 2003/2007 =MOD(1.5,0.05).

The subtle differences depend only on if and when subexpressions are converted from 80-bit to 64-bit binary floating-point internally.

For MOD(1.5,.0.05) per se, there is no issue with the implementation of Excel INT v. "true Int". TBD: Whether anomalies of Excel INT affect any examples of Excel MOD.

[ERRATA] Yes, generally they do. See the errata in the xlMod2003 and xlModForumla functions.

Note: The following emulations do not address defects with Excel MOD for certain combinations of parameters, notably KB 119083 (click here) [2].

The implementation of Excel 2003 and 2007 MOD can be emulated by the following:

Please Login or Register  to view this content.

The implementation of Excel 2010 MOD can be emulated by the following:

Please Login or Register  to view this content.

[EDIT] 1.5 is represented exactly internally, but 0.05 is approximated by
0.0500000000000000,0277555756156289135105907917022705078125. Note that the approximation is infinitesimally greater than the decimal fraction. So 1.5/0.05 is indeed less than 30 arithmetically.

For completeness....

The implementation of the Excel formula x - y*INT(x/y) can be emulated by the following:

Please Login or Register  to view this content.

The following implementation does not correspond to any Excel MOD result, AFAIK [1]:

Please Login or Register  to view this content.

-----
[1] I do not have Excel 2013 and Excel 2016 to test.

[2] http://support.microsoft.com/kb/119083