Results 1 to 7 of 7
  1. #1
    Lounger
    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 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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    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 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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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
  •