Results 1 to 3 of 3

Thread: IF formula comparing to numbers, and allowing for rounding
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2006
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default IF formula comparing to numbers, and allowing for rounding

    I have three cells.

    A20: 38.2
    B20: 39.3
    C20: contains IF formula to tell me if A1=B1, then "Good", if not, then "Check"

    To clarify, columns A & B contain numbers with decimals, row 20 would be the sum of all those numbers for each respective column.

    However, due to decimals, the totals won't always match up exactly. (e.g. 38=38 ). So i need to amend the if formula to still return "Good" if the numbers are within a margin of maybe 1?

    Or is there a better way to do this?


    not sure why i'm having a hard time with this, tried looking at other answers....

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: IF formula comparing to numbers, and allowing for rounding

    How about
    =IF(ABS(A20-B20)<1,"good","check")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,431
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: IF formula comparing to numbers, and allowing for rounding

    Rishijain, your examples are unclear to me.

    If A20 appears to be 38.2 and B20 appears to be 39.1 (instead of your 39.3) -- note that A20 is "within 1" of B20, and ABS(A20-B20)<1 is true -- do you really want to return "good"?!

    Or are you really talking about anomalies of binary floating-point arithmetic that cause the situation where A20 and B20 appear to be 38 (38.0), but in fact one might be 37.9999999999996 and/or the other might be 38.0000000000004?

    If that is the case, IMHO, the best approach is: when you expect a calculation to be accurate to a number of decimal places, explicitly round the calculation to that number of decimal places. (Not to an arbitrary number of decimal places, like 10, as some people suggest.

    In your example, the formulas in A20 and B20 might be of the form =ROUND(SUM(A1:A19),1).

    I choose 1 decimal place because that is the precision that you posted. You might choose something else.

    Alternatively, change the comparison to ROUND(A20,1)=ROUND(B20,1).

    And BTW, if you do want to compare only the "whole number", you might choose ROUND(A20,0)=ROUND(B20,0).

    But note that 38.2 and 39.3 will still result in "check" because 38<>39. (Again, your example is unclear to me.)

    If that does not give you the options that you want, please improve your examples, showing values with decimal fractions that should be "good", as well as examples with decimal fractions that should be "check".
    Last edited by joeu2004; Sep 15th, 2019 at 02:01 PM. Reason: critical typo in last ROUND example

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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