# Thread: Formula - False when Should be True (Xp SP-3)

1. ## Formula - False when Should be True (Xp SP-3)

Morning, Afternoon and Evening

I have a very strange calculation issue. I am adding two cells and then subtracting another cell and I then I am asking for either it to be true or false. Cells are formatted to Number with 2 decimals places and a comma for 1000 separator. I have done the math manually and the result should be true, yet false appears. Error checking gave me know clue. Calculation is set to Automatic. Any ideas would be helpful.

Here's the formula:
=IF(A11+B11-C11=D11,TRUE,FALSE)

Thanks and a million smiles
Kay
<img src=/S/confused.gif border=0 alt=confused width=15 height=20>

2. ## Re: Formula - False when Should be True (Xp SP-3)

Without knowing the actual numbers, I can only guess that this is due to rounding errors. Excel stores decimal numbers with finite precision, so calculations are not infinitely accurate. Try this:

=ABS(A11+B11-C11-D11)<1E-10

1E-10 = 0,0000000001 is just a very small number.

3. ## Re: Formula - False when Should be True (Xp SP-3)

Perhaps you meant

=IF((A11+B11-C11)=D11,TRUE,FALSE)

because of operator precedence? (See the Help on operator precedence.)

4. ## Re: Formula - False when Should be True (Xp SP-3)

Thanks I tried =IF((A11+B11-C11)=D11,TRUE,FALSE) AND =ABS(A11+B11-C11-D11)<1E-10, but I still get a False and an inconsistent formula error on both suggestions.

The Numbers are the following:

A11 11,787,376.76
B11 2,434,613.70
C11 13,763,569.66
D11 458,420.80
I truly appreciate all the help!
Thanks again!
Smiles
Kay

5. ## Re: Formula - False when Should be True (Xp SP-3)

Well, it turns out that the error is 7,567E-10, i.e. larger than 1E-10. So try 1E-9 or 1E-8 instead of 1E-10 in the formula I suggested.

In itself, the error is nothing to worry about. Since your largest number is in the order of magnitude of 10^7, an error of the order of magnitude of 10^-9 or 10^-10 is well within acceptable limits (a factor 10^16 smaller than the largest number itself.)

BTW, if you use =C11-A11=B11-D11, the formula will work OK, because you now subtract "like" numbers, but of course, that is highly dependent on the actual values.

6. ## Re: Formula - False when Should be True (Xp SP-3)

It is as Hans pointed out, a floating point discrepancy. I use something like:

=IF(ROUND(A11+B11,6)=ROUND(D11+C11,6),TRUE,FALSE)

to handle these situations.

7. ## Re: Formula - False when Should be True (Xp SP-3)

A side comment: I don't know if you are doing this for testing, but if you only want to return TRUE/FALSE, there is no need for the IF at all:
<pre>=ROUND(A11+B11,6)=ROUND(D11+C11,6)</pre>

To borrow from John's proposal will work and is more efficient.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•