Strange behaviour with MOD function

[whytrigg]

If anyone can help me understand what Excel is doing here, I would be very grateful.

I want to create a frequency chart basically using the % of something. I have a range of values between 0.00 and 1.00.

In an adjacent column I have the formula A1 - Mod(A1, 0.1)

for most of the results it behaves as expected, for example 0.71 returns the result 0.7, 0.8 returns the result 0.8 however, the value 0.6 returns the value of 0.5.

I have checked and there is no rounding in the target cell, the absolute value is "0.6".

Office support describes the mod function in line with my expectations " [FONT=&quot]MOD(n, d) = n - d*INT(n/d)"
[/FONT]
If I instead use the INT function as above I get the expected results.

Am I using the mod function incorrectly? what is it actually doing?

Thanks

David

 

[Scott Huish]

Re: Strange behaviour with MOD function - please help

It sounds like a floating point arithmetic issue, see here for more details: https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

But Excel has a function that does what you want:

=TRUNC(A1,1)

 

[Special-K99]

Re: Strange behaviour with MOD function - please help

If you multiply them all by 10 it works ok.
Same occurs with 2.6, 3.6, 4.6, 5.6

but not 1.6

0.600000000000001 produces 0.6
0.6000000000000001 produces 0.5

 

[whytrigg]

Re: Strange behaviour with MOD function - please help

Interesting, thank you both.

I rely so much on excel, I had never considered the way it stores numbers could lead to precision errors when I was only using 1.d.p.

David