IF formula comparing to numbers, and allowing for rounding

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
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....
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
How about
=IF(ABS(A20-B20)<1,"good","check")
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top