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

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.

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.

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 do-while loops. All looks great on paper but it isn't when you consider the problems with how computers do floating point math.

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

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 base-2 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 base-2 numbers with finite places. Meanwhile 0.15, 0.05 etc are not and there rounding errors are introduced within finite-precision 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... ;) )

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???

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.

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

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 error-correction 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.

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 base-10 calculation. But indeed 0.15/0.05 yields 3.0 - 2 ^ -51 in base-2 floating point calculation. This small error is result of the algorithm employed in double-precision 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 well-definedness.

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 well-defined.

After all, IEEE 754 is defined for scientific computation where maximal precision and well-definedness matter.

And as far as spreadsheet programme uses finite-precision 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 arbitrary-precision 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.

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.