MOD errors

[sbd85]

MAD AT MOD: In a column of formulae, the MOD function returns correc
values for most but not all values. MOD(9,.3) returns .3 for instance
not 0 (or a very small number). In a colimn of input values, the .
answer is returned from 9 to 18, but all other inputs correctly retur
0. Help

 

[Frank Kabel]

Hi
try
=MOD(9,3)

 

[JE McGimpsey]

If you're getting 0.3 as the result of =MOD(9, 0.3), then there's
something very wrong with XL.

If you're using a cell reference, check that the value in the referenced
cell is actually 9, not 9.3 set to display 0 decimal digits. Be aware
that the displayed value may not be the stored value, and unless you
have the Precision as displayed option on, XL calculates on the stored
value, not the displayed value.

If, OTOH, you're getting a very small number (on the order of 10^-16),
then that's a limitation of XL's IEEE Floating Point Double math, and
there's nothing you can do about it. As a workaround, you can use
ROUND():

=ROUND(MOD(9, 0.3), 5) ===> 0

where 5 is an arbitrary integer <=15