Asked by:
Excel 2010 MOD bug

In Excel 2010 entering a formula like =MOD(281.44,0.02) gives the answer 0.02 when it should be 0! In older versions of Excel (including 2007) a value around 1x1018 was given which could be seen as 0 but in 2010 it gives a whole 0.02 so is miles out! My colleague used MOD to round up an odd number of pennies (cents) to the nearest 2 but this function was rounding up 281.44 to 281.46. You can work around it by using the EVEN function with multiplication by 100 followed by division but surely the MOD function should be returning such a massively wrong answer?
 Moved by William Zhou CHNModerator Friday, July 1, 2011 5:53 AM Excel (From:Office 2010 Setup and Deployment)
Question
All replies

Is there anywhere we can report bugs? I'm afraid I posted this in a slightly less suitable MSDN forum but thought I would post it here in the hope I get a response.
If you try a formula like =MOD(2.50,0.02) you get the answer 0.02 and not 0! In older versions of Excel an answer like 1E18 is given which is fine as it shows as 0 when rounded to 2 dp but this bug is giving a large error, it nearly added 2 pence (2 cents) onto a customer's bill as we were using it to try and round to the nearest 2 pence (2 cents)! I would add that this bug also occurs when you use 0.01, 0.04, 0.05, 0.08, 0.1, 0.16, 0.2, 0.4 or 0.8 as the divisor, e.g. MOD(10,0.1) gives the answer 0.1
Edit: For completeness I decided to use 9999 as the value e.g. =MOD(9999,0.01) and tested every divisor between 0.001 and 1048.575 in increments of 0.001. This gave 97 values for which this bug occurs, the error only occurred when the answer should be 0, any nonzero remainders were returned correctly. The divisors that gave errors included 0.025, 0.101, 0.11, 3.636, 4.444, 5.555, 6.666, 7.272, 7.575, 16.665, 18.18, 19.998, 27.775, 60.6, 66.66, 99.99, 111.1, 166.65, 181.8, 333.3, 666.6 to name but a few. It obviously looks like higher values could cause the same problem.
Sorry to be sad, but I then tested divisors up to about 2096 and the bug didn't occur. It seems the highest divisor that causes the problem in this situation is 666.6 (even 999.9 is ok). Edited by All About Office Wednesday, June 29, 2011 3:46 PM more info
 Merged by David WoltersModerator Friday, July 1, 2011 5:06 PM Same issue, same user

Hi
Thank you for using Microsoft Office for IT Professionals Forums.
You created similar thread here: http://social.technet.microsoft.com/Forums/enUS/officeitpro/thread/6853dfbae78546ab8ebeed7f0f22bf9d
I will merge this thread.
I have been told that would be trying to involve someone for Developer with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
Thank you for your understanding and support.
Sincerely
William Zhou CHN

Please remember to mark the replies as answers if they help and unmark them if they provide no help. 

Sounds like a floating point precision issue. Interestingly, MOD was one of the functions rewritten to get rid of a long standing bug when the divisor was very (very) small relative to the number being divided.
While it does not fix the bug, an alternative formula to consider for your scenario would be FLOOR eg FLOOR(0.01+$A$1,0.02) will give you the value to the "nearest" 2 cents (ie 281.44 = 281.44, 281.449 = 281.44, 281.45 = 281.46)
Hope this helps Adam Vero MCT 
Yes, I know there are alternative ways of doing MOD, but the fact still remains the bug shouldn't be there as people will still use the MOD function and you can't be sure what divisor they will use.
You can take a precaution against the bug result by using something like
=IF(MOD(A1,B1)=B1,0,MOD(A1,B1))
as the MOD function should never return the divisor as the answer.

This problem reared its head again this week. Out of interest, I tried your suggestion of using FLOOR(0.01+A1,0.02) but there are still some numbers where the result is unacceptably way out, such as when A1=300.63 or 300.65, these are rounding down to 300.62 and 300.64 instead of up to 300.64 and 300.66.
A function like =EVEN(A1*100)/100 also gives wrong results, rounding 300.04 up to 300.06 when it should stay as 300.04

Bonsour®
"All About Office" wrote :
In Excel 2010 entering a formula like =MOD(281.44,0.02) gives the answer 0.02 when it should be 0!
In older versions of Excel (including 2007) a value around 1x1018 was given which could be seen as 0 but in 2010 it gives a whole 0.02 so is miles out!
My colleague used MOD to round up an odd number of pennies (cents) to the nearest 2 but this function was rounding up 281.44 to 281.46.
You can work around it by using the EVEN function with multiplication by 100 followed by division
but surely the MOD function should be returning such a massively wrong answer?don't use :
=MOD(A1,B1)
you better use this formulation :
=A1(INT(A1/B1)*B1)http://support.microsoft.com/kb/119083/enus
HTH
Maude Este 
Maude wrote:
don't use :
=MOD(A1,B1)
you better use this formulation :
=A1(INT(A1/B1)*B1)That relies on a defect in INT, IMHO. So I would not recommend it to solve this particular issue. Someday, I hope Excel fixes the INT defect.
For example, consider when A1 is 292.33 and B1 is 0.23. It is true that A1(INT(A1/B1)*B1) returns zero. But that is because INT(A1/B1) returns 1271.
Should it? I believe it should not.
First, note that in VBA, Int(292.33/0.23) returns 1270.
Second, note that A1/B11270 is 0.999999999999773 when formatted as Number with 15 decimal places. That demonstrates that 292.33/0.23 is less than 1271. Therefore, INT(A1/B1) should be less than 1271, namely 1270.
FYI, the root cause of the INT defect (IMHO) appears to be that Excel rounds the INT parameter to 15 significant digits before truncating to an integer. Although Excel displays the result of 292.33/0.23 as exactly 1271, the second observation above demonstrates that it is not. In fact, it is exactly 1270.99999999999,9772626324556767940521240234375.
Maude wrote:http://support.microsoft.com/kb/119083/enus
That defect has nothing to do with this issue. And in fact, that defect appears to be fixed in XL2010.
 Edited by joeu2004 Saturday, February 11, 2012 8:59 AM

"All About Office" wrote:
This problem reared its head again this week. Out of interest, I tried your suggestion of using FLOOR(0.01+A1,0.02) but there are still some numbers where the result is unacceptably way out
Right. Solving an inherent floatingpoint issue with another floatingpoint implementation is unlikely to be a real solution, in general.
"All About Office" wrote in 2011:
the fact still remains the bug shouldn't be there [....] the MOD function should never return the divisor as the answer.
First, you are mistaken that MOD(281.44,0.02), for example, returns the divisor 0.02 as the answer.
If you format the result as Number with 16 decimal places, you will see that the result is 0.0199999999999919. Since that is less than 0.02, MOD is correctly returning the remainder of the computer division.
I emphasize computer division in contrast to the mathematical division. That is the root cause of this anomaly. It is not a bug so much as an artifact of computer arithmetic in general and of the choices that Excel has made in representing numbers.
Computer arithmetic differs from mathematical arithmetic insofar as computer arithmetic is always limited by some finite precision. [EDIT] Consequently, most noninteger numbers cannot be represented exactly.
There are some implementations of computer arithmetic that minimizes the sideeffects of the finite limitation. The Microsoft Calculator accessory is a good example.
But Excel has chosen to represent numbers using 64bit floatingpoint (53 bits of precision) and to perform arithmetic using 80bit floatingpoint (64 bits of precision).
For the particular example of MOD(281.44,0.02), ironically it is the greater precision of 80bit floatingpoint arithmetic that is exposing the issue. If we convert each pairwise operation to 64bit floatingpoint representation, as the Excel formula A1B1*INT(A1/B1) does  an alternative to MOD(A1,B1)  MOD(281.44,0.02) does return exactly zero.
But that is merely a coincidence. We can find examples where even the pairwise 64bit floatingpoint conversion results in nonzero. For example, MOD(292.33,0.23) appears to return 0.23 despite the 64bit floatingpoint conversion  that is, until we format the result as Number with 15 decimal places and see that the result is actually 0.229999999999961. (It is 0.229999999999971 if we perform arithmetic using 80bit floatingpoint.)
The takeaway is: floatingpoint calculations are always at risk of not following the laws of mathematics. You can choose to grumble about it. Or you can choose to accept it as reality and do the necessary workarounds.
In the case of MOD, if your intent is to work with numbers with 2 decimal places as you indicated in a previous posting, I would suggest that you replace MOD(A1,B1) with MOD(ROUND(100*A1,0),ROUND(100*B1,0))/100.
Even better: ROUND(MOD(ROUND(100*A1,0),ROUND(100*B1,0))/100, 2). The outer ROUND(...,2) is for a slightly different reason. It ensures that the result of dividing by 100 is represented internally exactly the same as the equivalent constant.
Often you can get away without the outer ROUND(...,2). But infinitesimal floatingpoint anomalies might compound as you perform subsequent arithmetic operations. And they might cause surprises when using lookup and match functions.
 Edited by joeu2004 Saturday, February 11, 2012 9:42 AM