Problem using MOD (and INT) functions with some divisors less than 1

You are not doing anything wrong. What you get with paper and pencil may not be what you get with a computer that uses floating point math that follows the IEEE standard.This comes up as a topic on a regular basis on these forums but usually it is asked in relation to errors in simple math (addition subtraction, etc).
Computers have limited numbers of digits to represent numbers and not all numbers can be represented with a limited number of digits. As an example using base 10 to illustrate the problem, suppose you could use only four digits (not counting leading and trailing zeros) to represent your numbers on your piece of paper. If you divided 1 by 3 you would get 0.3333. Multiply that by 3 and you would get 0.9999 not 1. That's kind of what happens within computers except it is more complicated because computers also have to convert from base 10 into base 2 (binary) which also introduces errors because not all base 10 fractional numbers can be converted into exact base 2 numbers. I hope that makes some sense as an explanation of the underlying problem.
Sometimes you can get around the problem by getting everything into whole numbers by multipying by 10 or 100 or 1000, whatever it takes (i.e., =INT(priceA*100/5). This doesn't quite work in your examples of MOD and INT though; there are still errors. ROUND is also a handy function for this problem.
=ROUND(MOD(priceA*100/5),10)
=INT(ROUND(priceA/.05,10))
These are the kinds of things that also cause bugs in computer code, especially in dowhile loops. All looks great on paper but it isn't when you consider the problems with how computers do floating point math.

Like (0)


R,
While the function help does give an example using a decimal fraction for a divisor, the code for the modulo operation doesn't seem to be well vetted for anything other than Euclidean division.
I explored the 1.15 case and was able to duplicate the error you observed when multiplying by 100. However, if you arrive at 115 by direct entry rather than by multiplication, the error is avoided. That led me to try the following: =MOD(ROUND(100*1.15, 2), 5)
This eliminated the error in every case that I tested. So again, we are having fun with fractions in Numbers.
I think your observation is worth a Feedback report via the Numbers menu.
Jerry

Like (0)


Hiroto Japan
Hello
Other formulae which yield expected results are 
=MOD(ROUND(A*100,0),5)
=INT(ROUND(A*100,0)/5)
instead of 
=MOD(A,0.05)
=INT(A/0.05)
Indeed, =INT(A*100/5) works fine in this case as well but the above formula using ROUND() is safer in case A contains decimals.

(In addition to what Badunit said)
Only numbers in {n  n = ±∑ (a_{k} * 2^k), where a_{k} is 0 or 1 and k is finite integer} can be represented as exact base2 numbers with finite places. This is especially important for k < 0, which represents decimals.
In decimal region, 0.5, 0.25, 0.125, 0.0625, etc and arbitrary sum of them are represented as exact base2 numbers with finite places. Meanwhile 0.15, 0.05 etc are not and there rounding errors are introduced within finiteprecision binary floating point arithmetic in computer.
E.g.,
0.15 is represented as 3fc3333333333333 which is roughly equal to 0.14999999999999999.
0.05 is represented as 3fa999999999999a which is slightly larger than 0.04999999999999999.
0.15/0.05 is represented as 4007ffffffffffff which is slightly larger than 2.999999999999999.
MOD() are INT() are honest and thus 
INT(0.15/0.05) = 2
MOD(0.15/0.05) = 0.15  0.05 * INT(0.15/0.05) = 0.05
Generally speaking, it is very important practice to round decimals to some significant digits in context where small errors matter.
Specially speaking, it would be good practice to avoid decimals and only use integers in accounting because integer arithmetic is exact and not affected by binary floating point number errors. (E.g., instead 10.00, use 1000 by shifting 2 decimal places.)
Hope this helps,
H
Message was edited by: Hiroto (deleted wrong statement. I need my reading glasses... ;) )

Like (0)


Yellowbox New South Wales, Australia
Dear Spreadsheeters,
What a clever idea to use whole cents (integers) instead of floating point decimal dollars!
Jerry wrote:
"I explored the 1.15 case and was able to duplicate the error you observed when multiplying by 100. However, if you arrive at 115 by direct entry rather than by multiplication, the error is avoided."
That got me thinking about how to force Numbers to treat the result of multiplying by 100 as an integer.
How about this clunky approach. Turn the result into a number formatted as text, then use VALUE() to convert it back to an integer???
Dollars
Cents
Cents text
VALUE()
1.25
125
'125'
0
Cell B2 contains the number 1.25
Cell C2 contains the formula =B2*100 # result 125 as floating point
Cell D2 contains the formula =CONCATENATE("'"&C2&"'") # "'" is double quote, single quote, double quote and the result is '125'
I tried to make D2 become "125" i.e. double quotes as required by VALUE() but that did't work
However, we now have whole cents formatted as text. Next step is to convert text to a value, which *may* be the same as entering 125 from the keyboard.
Over to you.
Ian.

Like (0)


Thanks for the feedback, everybody. Thanks especially to Badunit and Jerry. Apple should be paying you guys!
I was curious how these functions fare in other spreadsheets, so I put Microsoft Excel to the test. The only copy I have at hand is not current, but is five or six years older than the version of Numbers I'm using; specifically MS Excel 2003 (11.8012.6568) SP2, on a 2005 Dell Dimension running Windows XP Home Edition.
The answer I came up with is: Excel wasn't perfect, but it performed considerably better than Numbers in handling my same two formulas.
With the formula using the MOD function Excel produced the same sorts of errors at first. But then I changed the formatting of the cells in the results column from "General" to "Number" and the errors were eliminated. With the INT function, Excel produced correct results either way.
So it seems that Microsoft's engineers have found a way to deal with this issue. Just guessing, but perhaps they've added some logic to their formatting schemes. Anyway I'm posting my results below in case somebody in Cupertino might decide this is worth fixing. I'm taking Jerry's advice and sending a Feedback report; I'll try to reference this discussion so if anyone has more to add, please reply here. (Anyone want to test Google Docs?) But for now I'd have to say, Advantage, Redmond...
Results using Excel 2003:
Format cells... "General" "General" "Number" "Number" priceA MOD(priceA, 0.05) INT(priceA/0.05) MOD(priceA, 0.05) INT(priceA/0.05) 0.05 0 1 0.00 1.00 0.10 0 2 0.00 2.00 0.15 1.38778E17 3 0.00 3.00 0.20 0 4 0.00 4.00 0.25 1.38778E17 5 0.00 5.00 0.30 2.77556E17 6 0.00 6.00 0.35 4.16334E17 7 0.00 7.00 0.40 0 8 0.00 8.00 0.45 1.38778E17 9 0.00 9.00 0.50 2.77556E17 10 0.00 10.00 0.55 1.38778E17 11 0.00 11.00 0.60 5.55112E17 12 0.00 12.00 0.65 1.38778E17 13 0.00 13.00 0.70 8.32667E17 14 0.00 14.00 0.75 4.16334E17 15 0.00 15.00 0.80 0 16 0.00 16.00 0.85 6.93889E17 17 0.00 17.00 0.90 2.77556E17 18 0.00 18.00 0.95 9.71445E17 19 0.00 19.00 1.00 5.55112E17 20 0.00 20.00 1.05 1.38778E17 21 0.00 21.00 1.10 2.77556E17 22 0.00 22.00 1.15 1.52656E16 23 0.00 23.00 1.20 1.11022E16 24 0.00 24.00 1.25 6.93889E17 25 0.00 25.00 1.30 2.77556E17 26 0.00 26.00 1.35 1.38778E17 27 0.00 27.00 1.40 1.66533E16 28 0.00 28.00 1.45 1.249E16 29 0.00 29.00 1.50 8.32667E17 30 0.00 30.00 1.55 4.16334E17 31 0.00 31.00 1.60 0 32 0.00 32.00 1.65 1.80411E16 33 0.00 33.00 1.70 1.38778E16 34 0.00 34.00 1.75 9.71445E17 35 0.00 35.00 1.80 5.55112E17 36 0.00 36.00 1.85 1.38778E17 37 0.00 37.00 1.90 1.94289E16 38 0.00 38.00 1.95 1.52656E16 39 0.00 39.00 2.00 1.11022E16 40 0.00 40.00 Mac OS X (10.6.8), Numbers '09, version 2.1 (436)
Like (0)


I tried Excel also (2011 version) but got different results than you did for the MOD formula. The results I got were similar to the results from Numbers. When formatted as "general", the results were exactly the same as what I got in Numbers when formatted as "automatic".
Note that Excel deviates from the IEEE standard for floating point math. It tries to detect those little errors I talked about and get rid of them. Sometimes it is successful, sometimes not, and sometimes it incorrectly zeros out perfectly correct (but very very small) results thinking they are errors. And what are the results of deviating from the standard in this case: Excel is giving us four different sets of answers for the exact same MOD formula.
These different results from different apps and from different versions of the same app highlight the problem even more. For robustness, you need to take care of any possible floating point math errors yourself. You cannot rely on the app to do it for you. It is kind of annoying but there is no better way.

Like (0)


Hiroto Japan
Hello Yellowbox,
ROUND() is the function to use to absorb unwanted small errors.
E.g., to convert floating point number 1.25 in colunm A to integer 125, you may use a formula such as 
=ROUND(A*100,0)
If you wish, you may implement the conversion via medium text "125" to integer 125 by 
=VALUE(""&A*100)
but it depends upon opaque conversion from number to text (in concatenation) in Numbers.app and thus I'm not sure it is guaranteed to work always as we expect.
Another way via text is 
=VALUE(FIXED(A*100,0))
this would be safe because FIXED(n, 0) actually rounds n to integer before converting it to text.
But then, the question is why not use ROUND(n, 0) in the first place?
After all, ROUND() is THE function to properly handle the errors in binary floating point arithmetic of finite precision.
I really think it should be well explained in compulsory education.
Regards,
H

Like (0)


Yellowbox New South Wales, Australia
Hello Hiroto,
Thank you. I shall remember that ROUND() is the function to use to absorb unwanted small errors. I have saved your message on my Mac as "Integers in s'sheets" for reference.
I remember some advice from an Information Technology friend at work: "The very first calculation that you do in a spreadsheet introduces an error. Every calculation after that compounds [and increases] that error".
Thank you for ROUNDING me up.
Ian.

Like (0)


So Excel deviates from the IEEE standard for floating point math. You seem to be suggesting that Numbers does not; perhaps this is what Hiroto means when he says, above, that MOD and INT are "honest"? OK, but what exactly is the benefit of adhering to a standard that produces incorrect results?
It seems to me that the spreadsheet programmers faced a strategic choice in this matter: and not for the first time Microsoft has gone one way and Apple another. Apple's choice may be defensible, maybe even the wiser choice (that wouldn't be a first, either). But at the least they should alert users to the effect of their choice, by addressing it in their own documentation. Maybe they should include the ROUND workaround described here, too.

Like (0)


In a debate I could defend or attack either one.
The good things about following the standard are that you will always get the same result for the same formula and you'll get the same result in other apps that follow the standard. The bad thing is it might not be the result you expected. By the standard it is the "correct" result, though
The good thing about trying to fix the little FP errors is that it more often gives you the results you expect. The bad thing is you are dependent on the errorcorrection algorithm employed by the app and you don't know what it is going to do. In the case of Excel, we've seen it spit out four different sets of answers for the exact same formula. That's not good.
It would be good if something was in the documentation.

Like (0)


Hiroto Japan
Hello RDPC,
I just meant INT() is honest to work as defined.
INT(x) returns the greatest integer that is less than or equal to x. Given x = 2.999999999999999, INT(x) = 2. Simple.
I grant that, in this specific case, INT(0.15/0.05) returning 2 looks not correct because we know 0.15/0.05 yields 3.0 in our base10 calculation. But indeed 0.15/0.05 yields 3.0  2 ^ 51 in base2 floating point calculation. This small error is result of the algorithm employed in doubleprecision binary floating point arithmetic defined in IEEE 754. Algorithm is carefully designed to offset average errors.
Now, in order to let INT(0.15/0.05) yield 3, we have to either deviate from IEEE 754 and arbitrarily round 3.0  2 ^ 51 to 3.0 or let INT() to deviate from its own definition and do this arbitrary rounding.
The problems of such arbitrary rounding are a) loss of precision and b) loss of welldefinedness.
Regarding a), in this specific case, 2 ^ 51 is error as we know, but in other cases it may be not and arbitrary rounding should reduce the precision which must have been kept as far as possible.
Regarding b), if INT() arbitrarily changes its behaviour depending upon given values it cannot be reliably applied on boundary values because its behaviour is not welldefined.
After all, IEEE 754 is defined for scientific computation where maximal precision and welldefinedness matter.
And as far as spreadsheet programme uses finiteprecision floating point math [1], I think users should learn the rules and always pay close attention to the presence of error in FPU calculation and use explicit rounding under user's full control when necessary.
Best wishes,
H
[1] Spreadsheet programme using arbitraryprecision arithmetic such as implemented in bc(1) is possible but I don't know of one. It would be slow, if any, even with modern PC.

Like (0)


A postscript to Badunit's observation that Excel produced four different sets of results for the same formula:
The results I got from Excel 2003 for the formula MOD(priceA,0.05) under the column formatted as "General" (shown in the column second from the left in the fivecolumn table in the Dec 30 5:41PM entry, above) changed after I saved the file and reopened it! All the results under this column in the reopened file were zeroi.e., the correct result, but different from the result shown just seconds earlier. I'd agree there's something amiss with producing "correct" results this way. (Though this might not be entirely Excel's doing; I suppose a Windows XP or even a hardware wrinkle could be involved.)

Like (0)


It is an Excel problem related to their optimization algorithm I assume. In Numbers you got 0's and 0.05's but if in another column you subtract 0.05 from all those supposed 0.05's you will get those same small error values Excel was giving you. Why it cleared up when you reopened the document is a mystery only Microsoft knows the answer to.

Like (0)
