arithmetic series problem

[mdlafont]

If I input the following arithmetic series with common difference 0.04:

A1 = 7.96
A2 = 8
A3 = 8.04
A4 = 8.08

Then, if I check each time whether the common difference is 0.04 using the following formula:

IF(A2-A1=0.04,"TRUE","FALSE")
IF(A3-A2=0.04,"TRUE","FALSE")
IF(A4-A3=0.04,"TRUE","FALSE")

I get the following results:

TRUE
FALSE
FALSE

If I further continue the series and the checking formula, the result continues to be false, even if I use a formula like A2 = A1 + 0.04 to set my values.

Can someone help me fix this bug?

Thanks in advance,

Matthias

[DonkeyOte]

Not a bug, see: http://support.microsoft.com/kb/78113

Using the example you would be best served rounding to level of significance required, eg: =ROUND(A2-A1,2)=0.04

[mdlafont]

Thanks for your reply. It is not a bug indeed. However, it is somehow a pity that Excel doesn't take this into account when evaluating among others such comparison formulas. Simple equality checks now come out as false negatives, which may cause a lot of trouble in macros or applications that base on Excel data.

[shg]

Excel doesn't have much choice -- blame IEEE 754-1985, the standard that defines the binary floating-point formats used by most or all computers.

On the other hand, there has been an alternative IEEE spec (854) that supports base 10 arithmetic, which has since been integrated into the latest version of 754 (2008), but is yet to see light of day in computers. So blame Intel.

The discussion and links at http://en.wikipedia.org/wiki/IEEE_754-2008 are informative.