IF formula comparing to numbers, and allowing for rounding

rishijain11

Board Regular
Joined
Mar 29, 2006
Messages
163
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....
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,626
Office Version
365
Platform
Windows
How about
=IF(ABS(A20-B20)<1,"good","check")
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,737
Office Version
2010
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,043
Messages
5,466,198
Members
406,472
Latest member
QuarkJaguar

This Week's Hot Topics

Top