Is this a bug?

[ElsiePOA]

I have a spreadsheet with numbers in Col. A. The numbers are betwee
.01 and 10.00. All numbers are with 2 decimal places. In Col. B,
have the formula "=MOD((A1*100),2)". The answer I'm expecting is
zero or a one and in most cases this is true. However if the number i
Col. A is 1.14, instead of a zero, I get "-1.42109E-14".

To see if this was unique to 1.14, I filled A1:A1000 with all of th
numbers between 0 and 10.00 in steps of .01 and put the formula i
B1:B1000. Most answers are zero or one, but in 67 of the 1000 numbers
I get a number similar to the one I referred to above.

I am using Excel 2000. Is this a bug in the application or do I have
corrupted program? Obviously I can't use the Mod function in thi
spreadsheet if the results are unreliable

 

[Frank Kabel]

Hi
it is not a bug but just caused by the binary represenattion of
decimals in computer programs in general. See:
http://www.cpearson.com/excel/rounding.htm

In your case use the following formula:
=MOD(ROUND(A1*100,0),2)

 

[Jerry W. Lewis]

=(1.14*100-114)
correctly returns the same value. As Frank has noted, the issue is
binary representation of numbers. Most decimal fractions cannot be
exactly represented in binary.

The binary representation of 1.14 is a non-terminating binary fraction,
just as the decimal representation of 1/3 is a non-terminating decimal
fraction. Since it is not possible to store an infinitely long value,
Excel and almost all other software except a few special purpose
packages use the IEEE 754 standard to determine where to terminate the
approximation. The IEEE approximation to 1.14 is slightly less than
1.14, but the difference is beyond the 15th figure so Excel will not
display it (per Help for "Excel specifications and limits" subtopic
"Calculation specifications"). When you multiply by 100, the result is
slightly less than 114, specifically
113.9999999999999857891452847979962825775146484375
so that subtracting 114 (equivalent to reduction modulo 2) correctly
gives 15 digits of
-1.42108547152020037174224853515625E-14.

A useful way to think about this without working out the decimal/binary
conversions it to recognize that in most cases only 15 digits of
floating point values are determined as you expect, thus
1.14000000000000???
*100
--------------------
114.000000000000???
-114
--------------------
0.000000000000???
which is consistent with Excel's reported value of
-0.0000000000000142108547152020

Where the difference between binary and decimal representations is
important to you, you can often get what you want through judicious use
of the ROUND function.

Jerry