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)
LinkBack URL
About LinkBacks
MOD function behaving badly
Register To Reply
Bookmarks