none
Excel 2010 MOD bug

    Question

  • 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 1x10-18 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?
    Tuesday, June 28, 2011 12:18 PM

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 1E-18 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 non-zero 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).
    Tuesday, June 28, 2011 8:58 PM
  • Hi

     

    Thank you for using Microsoft Office for IT Professionals Forums.

     

    You created similar thread here: http://social.technet.microsoft.com/Forums/en-US/officeitpro/thread/6853dfba-e785-46ab-8ebe-ed7f0f22bf9d

    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.
    Friday, July 1, 2011 8:10 AM
    Moderator
  • Is there any progress on this yet?  This issue could cause some very large errors in calculations if it is not addressed.
    Wednesday, August 3, 2011 12:26 PM
  • 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
    Thursday, August 4, 2011 1:25 PM
  • 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.

    Thursday, August 4, 2011 3:16 PM
  • 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

    Friday, February 10, 2012 10:29 AM
  • 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 1x10-18 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/en-us

    HTH


    Maude Este
    Friday, February 10, 2012 12:38 PM
  • 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/B1-1270 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/en-us

    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
    Saturday, February 11, 2012 8:12 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 floating-point issue with another floating-point 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 non-integer numbers cannot be represented exactly.

    There are some implementations of computer arithmetic that minimizes the side-effects of the finite limitation.  The Microsoft Calculator accessory is a good example.

    But Excel has chosen to represent numbers using 64-bit floating-point (53 bits of precision) and to perform arithmetic using 80-bit floating-point (64 bits of precision).

    For the particular example of MOD(281.44,0.02), ironically it is the greater precision of 80-bit floating-point arithmetic that is exposing the issue.  If we convert each pairwise operation to 64-bit floating-point representation, as the Excel formula A1-B1*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 64-bit floating-point conversion results in non-zero.  For example, MOD(292.33,0.23) appears to return 0.23 despite the 64-bit floating-point 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 80-bit floating-point.)

    The take-away is:  floating-point 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 work-arounds.

    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 floating-point 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
    Saturday, February 11, 2012 8:57 AM