excel bug

[omid84]

hi everybody
I think I find a bug in excel 2007 as follow:

select a cell and type this formula on it;
=IF(A1/0.3-INT(A1/0.3)>0;"yes";"no")
in cell a1 type 1.8 the formula shows "no" its correct
in cell a1 type 1 the formula shows "yes" its correct
in cell a1 type 1.5 the formula shows "no" its correct


but if type 2.1 or 2.5 or 2.7 on the cell a1 that formula shows "yes"
its very big mistake

generally excel have problem with this formula
check other numbers
what is your opinion?

[Richard Buttrey]

Hi,

No it's not a bug or error. Merely the well known problem associated with any computer system and floating point arithmetic.

See
http://support.microsoft.com/kb/78113

[estige]

Hi, this can certainly be a problem, but is not really a bug in Excel. This topic has been addressed a number of times in the forum. The following is copied from http://support.microsoft.com/kb/214118:

"Many combinations of arithmetic operations on floating-point numbers in Microsoft Excel and Microsoft Works may produce results that appear to be incorrect by very small amounts. For example, the equation =1*(.5-.4-.1) may be evaluated to the quantity (-2.78E-17), or -0.0000000000000000278 instead of 0.

This behavior is not a problem in or a limitation of Excel or Works; this behavior occurs because the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requires that numbers be stored in binary format.

Method 1
To minimize any effects of floating point arithmetic storage inaccuracy, use the Round() function to round numbers to the number of decimal places that is required by your calculation. For example, if you are working with currency, you would likely round to 2 decimal places:

=ROUND(1*(0.5-0.4-0.1),2)"

For more info, read for example the article "Floating-point arithmetic may give inaccurate results in Excel", which you can find here:http://support.microsoft.com/kb/78113/en-us