The MOD bug

My attention has been drawn to an article in a UK magazine about an error in Excel when using the MOD function. This error doesn't happen with all numbers, but it does happen with more than enough to make it a worry, especially if you rely on the MOD function in your calculations.

Take a simple number: 1.23 and then multiply it by 10 for a second number 12.3
Now, I'll use these two numbers to demonstrate the error.

But first, a short trip into the Excel 97 help file where you will find the following remarks about the MOD function


The MOD function can be expressed in terms of the INT function:

MOD(n, d) = n - d*INT(n/d)
Below is an extract from an Excel 97 workbook which uses the above formula to prove that the result is zero, and then the same figures in the MOD function generating an error.
   Missing: Microsoft Office Web Components
This page requires the Microsoft Office Web Components.

Click here to install Microsoft Office Web Components..


Click here to install the latest Internet Explorer.

In this example I have named cell A1 as "n"
..and cell A2 as "d"
In cell A3, using the help file formula,
I get the expected answer.
In cell a4, when using MOD, Excel generates an incorrect result.
They say that size doesn't matter, but when multi-billion pound deals might depend on miniscule fractions of a pound to generate a profit, as they sometimes do in the financial markets, then errors such as this can become significant, but it gets worse.
The article goes on to show another, potentially more serious error, in the MOD function used in VBA. Below is an extract of VBA code. Using the formula from the help file the result is zero, which is correct. However, using the VBA's MOD, the result is 1, which is clearly incorrect.

Sub modbug()
Dim n
Dim d

n = 23.4
d = 2.34

MsgBox n - (d * Int(n / d))
MsgBox n Mod d

End Sub

The reason is that in VBA the numbers are converted to integers BEFORE the mod calculation. So here n becomes 23 and d becomes 2 leaving a remainder of 1.
Reference: Jon Honeyball, PC Pro, December 1999