Why does the modulo function in excel return the divisor?

Question

I have an Excel validation formula which worked fine in Office '03, '07, but is failing in 2010. Did they change the definition of MOD recently?

In 03/07 the following returns zero:

=MOD(1, .05)

However in 2010 it returns .05.

As far as I can tell they haven't changed how remainders worked since I was in 3rd grade when I learned about them. However, Microsoft may be taking certain liberties.

[Similar issue (floating point error) for Python](https://stackoverflow.com/questions/4218961/why-fmod1-0-0-1-1). — user202729, Jul 27 '18 at 13:08

score 0 · Answer 1 · answered Mar 17 '11 at 20:34

0

Actually... it does not return zero in Office 2007, but -0.000000000000000056 (just tested).

Maybe it's a floating point issue or the way Excel 2010 handles floating point changed.

answered Mar 17 '11 at 20:34

Tiago Cardoso

2,057
1
14
33

Excel 2010 did fix a bug with the MOD function http://en.wikipedia.org/wiki/Microsoft_Excel#Excel_MOD_function_error.It looks to me like they introduced another one, but you'd need to find the definition of the function for a non integer divisor. – paulmorriss Mar 18 '11 at 10:24

score 0 · Accepted Answer · answered Mar 18 '11 at 16:17

0

So I finally decided the best way to determine this was to cheat and do:

=(1/.05)-(floor(1/.05))

That gives me the zero I need.

answered Mar 18 '11 at 16:17

Chris Pfohl

18,220
9
68
111

Why does the modulo function in excel return the divisor?

2 Answers2