The Internet Archive discovers and captures web pages through many different web crawls.
At any given time several distinct crawls are running, some for months, and some every day or longer.
View the web archive through the Wayback Machine.
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.
In this example I have named cell A1 as "n"
..and cell A2 as "d"
In cell A3, using the help file formula,
n-(d*INT((n/d)))
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.
