1. ## 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. ## 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

3. ## 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

4. ## 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:
`Please Login or Register  to view this content.`

Drag it down...

5. ## Re: MOD function behaving badly

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

6. ## 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. ## 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. ## 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. ## 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.

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