Excel modulus function MOD(0.9, 0.3) returns unexpected result

Question

MOD function in Excel should return the Modulo Remainder in Excel

I believe the function =MOD(0.9, 0.3) should give 0 since basically 0.3*3 is 0.9

But it gives 5.55112E-17

Any hints to what's wrong?

Answer 1

It's not a bug! Excel uses IEEE-754 double precision, and like any other binary floating-point types, it's impossible to represent decimal values like 0.9 or 0.3 exactly. The actual values are just very close to them,
^{0.90000000000000002220446049250313080847263336181640625} and
^{0.299999999999999988897769753748434595763683319091796875} to be exact.

The result of the modulo operation is
_{5.5511151231257827021181583404541015625 × 10^-17} which is a very very small value, almost zero and Excel displays it rounded to 5.55112E-17 so there's nothing wrong here! Almost all computer programming languages use binary floating-point and will produce the same result for the same such operations

To fix it you can:

• Change the number of display decimal digits or round the result manually to 1 decimal digit (the same as the number of decimal digits in the inputs) and Excel will show 0 correctly, or
• Use VBA which has its own decimal type (and also integer types with higher precision). You can do operations in VBA directly or write user-defined functions for use in Excel

Repeating binary numbers and calculations that have near-zero results

Another confusing problem that affects the storage of floating point numbers in binary format is that some numbers that are finite, non-repeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is the value 0.1 and its variations. Although these numbers can be represented perfectly in base 10, the same number in binary format becomes the following repeating binary number when it is stored in the mantissa:

000110011001100110011 (and so on)

The IEEE 754 specification makes no special allowance for any number. It stores what it can in the mantissa and truncates the rest. This results in an error of about -2.8E-17, or 0.000000000000000028 when it is stored.

Even common decimal fractions, such as decimal 0.0001, cannot be represented exactly in binary. (0.0001 is a repeating binary fraction that has a period of 104 bits). This is similar to why the fraction 1/3 cannot be exactly represented in decimal (a repeating 0.33333333333333333333).

Further reading

• Floating-point arithmetic may give inaccurate results in Excel on detailed Excel implementation and methods to fix/workaround the issue
• Is floating point math broken? for technical reasons affecting all applications that use binary floating-point
• Numeric precision in Microsoft Excel
• What Every Programmer Should Know About Floating-Point Arithmetic
• What Every Computer Scientist Should Know About Floating-Point Arithmetic

Answer 2

In my case MOD(0.15,0.05) returns 0.05 when it should be zero (0.05 goes exactly three times in 0.15). This looks like a bug.

So I avoid the MOD() function and use instead:

num1/num2 - trunc(num1/num2))*num2

rounded off to say 10 decimal places.

Answer 3

Excel uses Visual Basic on its programming. So, Microsoft refers on mod operation as

Floating-point Imprecision
When you work with floating-point numbers, keep in mind that they do not always have a precise representation in memory. This could lead to unexpected results from certain operations, such as value comparison and the Mod operator.

Answer 4

It seems to be floating point bug in Excel

http://www.excelforum.com/excel-formulas-and-functions/927962-mod-function-behaving-badly.html