Precision problem while using the MOD function

Floating point errors are just an inherent part of the way computers do arithmetic. More than you probably want to know about floating point calculations and associated errors: https://www.excelforum.com/groups/ma...nd-errors.html

Usual solutions are to use a rounding function, but it sounds like you have already tried that, though you don't specify exactly how you tried using these rounding functions. I would have expected something like =ROUND(MOD(11/10,1),0) to return exactly 10. Have you tried something like that?

Originally Posted by Pedro Luis

I tried the rounding function instead, but it gives me the same error; as well as the floor.
Whats weird is that it only happens for 11/10 when I enter straight 0.1, the inverse has no precision problems.

What's your exact ROUND formula? C3: =ROUND(1/MOD(11/10,1),0) displays 10, D3: 10 displays 10 (no surprise), E3: =C3=D3 returns TRUE.

Excel does some implicit truncation handling for SOME formulas, e.g., C3: =1/MOD(11/10,1) displays 10, D3: =C3-10 displays 0, and E3: =D3=0 returns TRUE. However, F3: =(C3-10) displays and returns -8.88178E-15. Should =C3-10 and =(C3-10) return different values? Even if the answer is definitely not, that's what Excel does.

Anyway, details on 1/MOD({11..19}/10,1).

	A	B	B displays
1	=1/MOD(11/10,1)	=(A1-10)	-8.88178E-15
2	=1/MOD(12/10,1)	=(A2-10/2)	8.88178E-16
3	=1/MOD(13/10,1)	=(A3-10/3)	-4.44089E-16
4	=1/MOD(14/10,1)	=(A4-10/4)	4.44089E-16
5	=1/MOD(15/10,1)	=(A5-10/5)	0
6	=1/MOD(16/10,1)	=(A6-10/6)	-2.22045E-16
7	=1/MOD(17/10,1)	=(A7-10/7)	0
8	=1/MOD(18/10,1)	=(A8-10/8)	0
9	=1/MOD(19/10,1)	=(A9-10/9)	0

The reason row 5 shows 0 in col B is because 15/10 is exactly represented in binary floating point, so MOD(15/10,1) is exactly 0.5, and its reciprocal is exactly 2.

As for rows 7 to 9, they show 0 because the truncation errors are too small for Excel to represent in 15 decimal places. It's not that binary floating point represents them exactly. Rather that it'd take more than 52 mantissa bits to represent the difference.

Why is 11/10 different from the others? Order of magnitude of the truncation error. The others are < 1E-15 in absolute value, but 11/10 is > 1E-15 in absolute value. This is actually pure happenstance. You'll get different results for denominators that are products of powers of primes other than 2 and 5.