Results 1 to 7 of 7

20040325, 13:44 #1
 Join Date
 May 2002
 Location
 Cleveland, Ohio, USA
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula  False when Should be True (Xp SP3)
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+B11C11=D11,TRUE,FALSE)
Thanks and a million smiles
Kay
<img src=/S/confused.gif border=0 alt=confused width=15 height=20>

20040325, 13:51 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula  False when Should be True (Xp SP3)
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+B11C11D11)<1E10
1E10 = 0,0000000001 is just a very small number.

20040325, 13:52 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Formula  False when Should be True (Xp SP3)
Perhaps you meant
=IF((A11+B11C11)=D11,TRUE,FALSE)
because of operator precedence? (See the Help on operator precedence.)John ... I float in liquid gardens
UTC 7ąDS

20040325, 15:06 #4
 Join Date
 May 2002
 Location
 Cleveland, Ohio, USA
 Posts
 34
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula  False when Should be True (Xp SP3)
Thanks I tried =IF((A11+B11C11)=D11,TRUE,FALSE) AND =ABS(A11+B11C11D11)<1E10, 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

20040325, 15:17 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula  False when Should be True (Xp SP3)
Well, it turns out that the error is 7,567E10, i.e. larger than 1E10. So try 1E9 or 1E8 instead of 1E10 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 =C11A11=B11D11, the formula will work OK, because you now subtract "like" numbers, but of course, that is highly dependent on the actual values.

20040325, 15:31 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Formula  False when Should be True (Xp SP3)
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.John ... I float in liquid gardens
UTC 7ąDS

20040325, 15:35 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula  False when Should be True (Xp SP3)
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