+ Reply to Thread
Results 1 to 7 of 7

IF function giving wrong result

  1. #1
    Registered User
    Join Date
    12-01-2003
    Posts
    6

    IF function giving wrong result

    hi, wrote an IF function and it seems to be giving the wrong results, and i need help troubleshooting.

    i have these figures in the following cells:
    A1: 220
    B1: 220
    C1: 0
    D1: 0.83
    E1: 175.5
    F1: 7.1

    basically i'm trying to verify that F1 is equal to a formula involving A1 to E1.

    i had this function: =IF(((A1*D1)-((C1*E1)/B1)-E1)=F1,"correct","wrong")

    and it keeps returning 'wrong', even though the results match.

    sigh. i've been wrecking my brains over this for two days. any advice would be much appreciated!

    -kenneth

  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Mathematically the result is not matching acurately. The left hand side evaluates to 7.099999 and not 7.1 and hence the behaviour you are observing.

    You may change your function to:
    =IF(ROUND(((A1*D1)-((C1*E1)/B1)-E1),4)=F1,"correct","wrong")

    Note that here I am rounding the LHS result to 4 decimals, which you may vary, and then comparing it with 7.1

    - Mangesh

  3. #3
    Registered User
    Join Date
    12-01-2003
    Posts
    6
    thanks, but why do you say that mathematically it's 7.099999?

  4. #4
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Try entering just
    =(A1*D1)-((C1*E1)/B1)-E1
    in a cell and check out the result for yourself.

    - Mangesh

  5. #5
    Registered User
    Join Date
    12-01-2003
    Posts
    6
    i tried that and still got 7.1. and mathematically it shouldn't be 7.09999999 because no portion of that formula should create that many decimal places...

  6. #6
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    Hi!

    Excel works with binary approximations to decimal fractions, much as decimal numbers work with approximations to fractional numbers. ( Think of 1/3 : exact. Think of 0.33333.........:never exact)

    Excel also works only to 15 significant figures.

    Within these two constraints lies your problem.

    There are workarounds such as if abs(a-b)<0.00000001 then do something. This acknowledges that there may be a whisker of difference between the numbers which doesn't matter.

    If it is critical that every digit must count, you could try adjusting your numbers so that you are dealing entirely with integers (again, subject to the 15 sig. fig. limit) or get a program which goes beyond 15 sig. fig. accuracy

    Alf

  7. #7
    Registered User
    Join Date
    12-01-2003
    Posts
    6
    thanks much for the explanation! very good tip to keep in mind from now on. you folks are swell!

+ 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