Repair MOD & INT Function in Excel 2003. Errors currently exist.

[sgunther]

The MOD function exibits an error.

Start with 8 in A1
Increnet A2 to A32 by 0.1; (A2=A1+0.1) (A3=A2+0.1)
Bx = INT(Ax); (B2 = INT(A2)) (B3 = INT(A3))
Cx = MOD(Ax,1); (C2 = MOD(A2,1))(C3 = MOD(A3,1))

Notice that when Column A = 9, 10, and 11 that MOD exibits errors;
For A=9, MOD= -3.55271E-15
For A=10, MOD= 1
For A=11, MOD= -1.06581E-14

But if you manually do an MOD(9) or MOD(10) or MOD(11) you get the correct
results.

The INT function fails in the above when;
For A=10, INT= 9

But if you manually do an INT(10) you get the correct results.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...0011e58&dg=microsoft.public.excel.crashesgpfs

 

[Bill Manville]

Real numbers are (mostly) not capable of exact representation in a
finite number of bits. Excel, in common with most software, uses a
"floating point" representation of real numbers. 0.1 is an example of
a real number that cannot be held exactly. It will have has a rounding
error of the order of 1E-16. When you add another 0.1 to the initial
value you will also be adding the 2 rounding errors.

Excel typically ignores the smallest of possible rounding errors when
displaying a result, but when the errors have accumulated they are
shown.

I see different results from those that you report; however, I do see
the small rounding errors being reported for the MOD function when the
value in A is an integer plus an accumulated rounding error.
This is what I would expect since N + delta - N = delta

If you change the formulas in column A so as to avoid the accumulation
of rounding errors (e.g. A1 = 0.1*ROW() ) I think you will find the
results are as you would expect. Alternatively, scale everything up by
10 so that the values are integers, held exactly, and then divide the
final results by 10. Or round the results to an approporiate number of
decimal places (=ROUND(MOD(A1,1),8))

It is always a mistake to expect arithmetic on real numbers to be
performed exactly.

Bill Manville
MVP - Microsoft Excel, Oxford, England