IF formula comparing to numbers, and allowing for rounding

[banneduser123]

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....

 

[Fluff]

How about
=IF(ABS(A20-B20)<1,"good","check")

 

[joeu2004]

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".