Why does Microsoft Excel evaluate MOD($44$, $8.8$) as $8.8$?

Question

As per my understanding, the formula function MOD in Microsoft excel should give the remainder value, after dividing two numbers.

So the formula =MOD(40,8) gives a value 0. This works as expected.

But the formula =MOD(44,8.8) gives a value 8.8, even-though 8.8*5 = 44.

Could anyone help me understand why this gap?

This is really a question for Microsoft. For instance, see this Microsoft Community item "Excel MOD fuction returns non zero remainder when there is no remainder" and others. Evidently, MOD's behavior is inconsistent with non-integer values. — Blue, Jun 15 '22 at 20:38

score 1 · Answer 1 · answered Jun 15 '22 at 20:31

1

I think it has to do with the fact that computers truncate numbers when they round them. You can try for example:

=MOD(44,8.8)

=MOD(44,8.7999999999)

They provide different results even though they should be almost the same.

If you try a similar experience with an exact power of $2$ then the computer can compute the division without any loss of precision.

=MOD(2.5,0.5)

answered Jun 15 '22 at 20:31

PC1

Sorry, you were right. – mathcounterexamples.net Jun 15 '22 at 20:39
No worries! I got bitten many times with this problem... – PC1 Jun 15 '22 at 20:40
2

Apple's Numbers spreadsheet program and LibreOffice get this right. If I run a simple C program to do the calculations in the obvious way using 32 bit floating point arithmetic, I get the right answer too (which is what I'd expect given the numbers involved here). So Microsoft's implementation is doing something odd, but I agree that you shouldn't rely on floating point approximations giving precise results. Moral: don't use MOD without first converting the operands to integers in a way that is under your control. – Rob Arthan Jun 15 '22 at 22:00

1 Answers1