MOD function not returning exact zero

[SB]

Hi there!

I am working with the excel MOD function.
MOD(n,d)

n = 2929.68750000
d = 418.5267857

I expect the answer to be zero.
However, Excel is returning: -5.68434E-14

I have numerous incidences of this in my spreadsheet and
these values are messing up the worksheet

How does this occur and, more importantly, is there any way to
resolve this?

Regards
SB

 

[JE McGimpsey]

It occurs because, just as most numbers cannot be exactly represented by
a fixed number of decimal digits, most numbers cannot be exactly
represented by a fixed number of binary digits, which is what XL (and
every other spreadsheet) uses to store and calculate with.

You can use =ROUND(MOD(n,d),x) to fix the problem with an arbitrary
precision (e.g., x=10 rounds to the tenth decimal place).

See here for more:

http://cpearson.com/excel/rounding.aspx

 

[Guest]

Hit the comma button in your menu bar and it should return it as 0, if not
you will have to round.