+ Reply to Thread
Results 1 to 9 of 9

MOD function behaving badly

  1. #1
    Registered User
    Join Date
    06-02-2013
    Location
    Mel, Aus
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Question MOD function behaving badly

    Hi all,

    New member here. I am using Excel 2011 for Mac, and I am having a problem with the MOD function. In the previous versions(2007, and earlier back to version 1.0, yes I used the first Mac version, yes I am old) it worked fine.

    What happens is this:
    MOD(0.3,0.1) results in 0.1, the expected result is 0
    I am using the MOD function for data validation(with conditional formatting) of money denominations, (100, 50, 20, 10, 5, 2, 1, 0.50, 0.20, 0.10, 0.05), and they result in similar, intermittant, errors for many tested decimal fractions after a factor of 3. Here are a few I have done in basic testing.
    In every case, the first column(A1=MOD(B1,C1) should be equal to 0, as the number(second column:B) is always a whole multiple of the divisor(third column:C).

    Formula is:
    "MOD(B3,C3)
    0 0.1 0.1
    0 0.2 0.1
    2.77556E-17 0.3 0.1
    0 0.4 0.1
    0.1 0.5 0.1
    0.1 0.6 0.1
    0.1 0.7 0.1
    0.1 0.8 0.1
    0.1 0.9 0.1
    0.1 1 0.1
    0.1 1.1 0.1
    0.1 1.2 0.1

    0 0.2 0.2
    0 0.4 0.2
    5.55112E-17 0.6 0.2
    0 0.8 0.2
    0.2 1 0.2
    0.2 1.2 0.2
    0.2 1.4 0.2
    0.2 1.6 0.2
    0.2 1.8 0.2
    (sorry for the hard to read table, the table function here is a bit of a nightmare for the uninitiated)


    This apparent error makes data validation via this method impossible for decimal fractions (any value under a dollar), though it does not seem to apply to whole numbers (or at least tested numbers above 1).

    Any thoughts/suggestions? I am really tempted to go back to the previous version, where although I do not have access to VBA, at least this formula works(and conditional formatting is not such an effing mess)

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: MOD function behaving badly

    Seems to be failing in this case
    MOD(0.3,0.1)

    Just convert it as integer and do the calculation like this...
    =MOD(0.3*10,0.1*10)

    Your other examples are not clear for me since don't know which data belongs to which cell, so if possible attach a sample workbook with expected output for better understanding


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    06-02-2013
    Location
    Mel, Aus
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: MOD function behaving badly

    Hi Sixthsense,

    Thanks for the lightning fast reply

    A test file is attached below. I have included a paste of the values only, just in case the problem is not replicated on your version/platform.

    Oh and your initial solution is excellent, I have implemented it already and it works fine, shame it is necessary though...

    Cheers,
    MZG
    Attached Files Attached Files
    Last edited by mzg71; 06-03-2013 at 03:25 AM. Reason: PS

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: MOD function behaving badly

    MOD failing in many cases in your attached file

    This is seems to be better approach...

    In A2 Cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag it down...

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: MOD function behaving badly

    the mod function algorithm was changed in 2010 (and probably 2011)-looks like they introduced a bug
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  6. #6
    Registered User
    Join Date
    06-02-2013
    Location
    Mel, Aus
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: MOD function behaving badly

    Sixthsense,

    Thank you, that is some nice math, an elegant formula for MOD. I take my hat off to you sir.

    Cheers,
    MZG

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,351

    Re: MOD function behaving badly

    The problem is due to MS implementing of floating point arithmetic - See http://support.microsoft.com/kb/78113 and http://answers.microsoft.com/en-us/o...b-68b599b31bf5

    It is NOT a bug although it looks like it

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: MOD function behaving badly

    if you change the behavior of a function so that it produces different results for the same inputs, I call it a bug (unless it's explicitly a bug-fix) :-)

    note: the change in behavior is not directly related to floating point arithmetic since that was always in play

  9. #9
    Registered User
    Join Date
    06-02-2013
    Location
    Mel, Aus
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    4

    Re: MOD function behaving badly

    I have to agree with Jo. If the function gives a result which is patently incorrect, then it is a bug.

    You couldn't have 1+1=3, and then say that is not a bug because you use some special type of math to get the answer.

    Edit: And to add to floating point math being used as an excuse, if Sixthsense's solution works perfectly accurately, then how can floating point math be an issue? They just need to redefine how the function is calculated to reflect a solution which accurately mimics the previous function.
    Last edited by mzg71; 06-04-2013 at 03:09 AM. Reason: clarification

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1