+ Reply to Thread
Results 1 to 5 of 5

Formula behaves differently

  1. #1
    Registered User
    Join Date
    02-22-2006
    Posts
    28

    Formula behaves differently

    Hi,

    I have a peculiar problem. I display TRUE or FALSE in a cell and is based on the below formula.
    =(IF(ISNUMBER($B66),($AK66=($P66-$V66+$AA66+$AB66-$AF66)),""))

    But the value displayed is FALSE. The value in AK66 is equal to 650.38 and the sum of ($P66-$V66+$AA66+$AB66-$AF66) is also equal to 650.38.

    The same formula is copied to the entire column and it shows TRUE for other cells and is as expected. I tried with several values.

    Can anybody help me out pls.

  2. #2

    Re: Formula behaves differently

    Excel stores it's numbers to a 15 Decimal Place precision, so it's
    probably an internal rounding problem - if you force excel to round by
    using the round function, you will resolve this

    =round(ak66,2)=round(etc etc etc,2)


  3. #3
    Guest

    Re: Formula behaves differently

    Hi

    Your formula looks a bit odd! It is saying that if B66 is a number, then
    $AK66=($P66-$V66+$AA66+$AB66-$AF66).
    Eh?
    Are you trying to compare AK66 to $P66-$V66+$AA66+$AB66-$AF66? See if this
    does what you want:
    =IF(AND(ISNUMBER($B66),$AK66=$P66-$V66+$AA66+$AB66-$AF66),"Yes","No")
    This is untested - and might not be what you want anyway!

    Andy.

    "mtpsuresh" <[email protected]> wrote
    in message news:[email protected]...
    >
    > Hi,
    >
    > I have a peculiar problem. I display TRUE or FALSE in a cell and is
    > based on the below formula.
    > =(IF(ISNUMBER($B66),($AK66=($P66-$V66+$AA66+$AB66-$AF66)),""))
    >
    > But the value displayed is FALSE. The value in AK66 is equal to 650.38
    > and the sum of ($P66-$V66+$AA66+$AB66-$AF66) is also equal to 650.38.
    >
    > The same formula is copied to the entire column and it shows TRUE for
    > other cells and is as expected. I tried with several values.
    >
    > Can anybody help me out pls.
    >
    >
    > --
    > mtpsuresh
    > ------------------------------------------------------------------------
    > mtpsuresh's Profile:
    > http://www.excelforum.com/member.php...o&userid=31817
    > View this thread: http://www.excelforum.com/showthread...hreadid=543761
    >




  4. #4
    Jerry W. Lewis
    Guest

    RE: Formula behaves differently

    I know of no documented instance of basic arithmetic errors in Excel.

    Excel and almost all other computer software does binary arithmetic. Most
    terminating decimal fractions (including 650.38) are non-terminating binary
    fractions that can only be approximated (just as 1/3 can only be approximated
    as a decimal fraction).

    Likely your calculation simply resulted in a different binary approximation
    to 650.38 due to the binary approximations to the initial inputs. With
    addition/subtraction, you can simply round the final result to smallest
    decimal place of any of the inputs without doing violence to the calculation.

    My D2D function at
    http://groups.google.com/group/micro...06871cf92f8465
    will allow you to more closely examine the approximations involved, if you
    so desire.

    Jerry

    "mtpsuresh" wrote:

    >
    > Hi,
    >
    > I have a peculiar problem. I display TRUE or FALSE in a cell and is
    > based on the below formula.
    > =(IF(ISNUMBER($B66),($AK66=($P66-$V66+$AA66+$AB66-$AF66)),""))
    >
    > But the value displayed is FALSE. The value in AK66 is equal to 650.38
    > and the sum of ($P66-$V66+$AA66+$AB66-$AF66) is also equal to 650.38.
    >
    > The same formula is copied to the entire column and it shows TRUE for
    > other cells and is as expected. I tried with several values.
    >
    > Can anybody help me out pls.
    >
    >
    > --
    > mtpsuresh
    > ------------------------------------------------------------------------
    > mtpsuresh's Profile: http://www.excelforum.com/member.php...o&userid=31817
    > View this thread: http://www.excelforum.com/showthread...hreadid=543761
    >
    >


  5. #5
    Registered User
    Join Date
    02-22-2006
    Posts
    28
    Thanks for your replies.

    The problem was in the decimal fractions as all of you have pointed out. I've corrected it.

    Once again thanks for all your valuable time and effort for me.

    Regards
    Suresh

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1