Mod Returning Different Outputs When Using Formula

Question

I am getting conflicting results.

With the formula:

=MOD(1,1)

I get 0 as one would expect.

But if I come to that 1 by formula:

=MOD((1.2-1)/0.2,1)

I get 1.

Even if I split out the formula into its parts:

=(1.2-1)/0.2

and

=MOD(B5,1)

I get 1

So the question is, "Why the difference?"

Answer 1

Examining the Worksheet XML to see how Excel is storing the calculation results internally to 17 decimal digits can shed some light on what is going on with regard to the IEEE specifications and Excel's precision limits.

The bottom line is that the result of 1.2-1 is slightly less than 0.2; and that numbers slightly less than 1 (at digits past Excel's precision limit) get displayed as 1

A1:  MOD((1.2-1)/0.2,1)  --> 0.99999999999999978

A2:  1.2-1 --> 0.19999999999999996

A3:  A2/0.2 --> 0.99999999999999978

A4:  (1.2-1)/0.2 --> 0.99999999999999978

Answer 2

For the sake of posting an answer... this seems due to floating-point error.

A couple examples/points that corroborate this:

• ROUNDing fixes the issue: =MOD(ROUND((1.2-1)/0.2,0),1) returns 0.
• A simpler formula, e.g. =MOD(1*1,1), returns 0.
• A slightly more simple formula, e.g. =MOD(1.2-0.2,1) returns 0.