Solved How do I utilize If/Then with a variance =+/-.3

January 31, 2017 at 19:51:41
Specs: Windows 7
I have 3500+ lines of payroll data that requires me to join names for a helper column and compare hourly wages, total paid, total hours, plus bonuses from two separate spreadsheets. One is a billed hours report, the other a remittance report.

I am trying to create an If/Then statement that will check to see if value in E2=N2+02 with a variance <=>.03 of E2

This is what I have:
E2=377.24(total payables)
N2=358.18 (total hour wages paid)
O2= 19.06 (bonus awarded)

My formula (or as far as I can get it) is:
=if(E2=((N2+O2)<=>.3)),"Match","N/A")

ANY help would be GREATLY appreciated. I am wanting to set this up as a template so I just have to change out the actual data and the formulas just recalculate as needed.


See More: How do I utilize If/Then with a variance =+/-.3

Report •

#1
January 31, 2017 at 20:55:09
✔ Best Answer
I'm a bit confused. In your subject line you use .3. In your formula attempt you use .3. Yet in your text you use .03.

Regardless, if I understand your requirements correctly, what you need to do is use an AND function to check if the sum of N2 and O2 is both "Less Than or Equal To" E2 plus your variance and "Greater Than or Equal To" E2 minus your variance.

When both of those conditions are TRUE, the AND will be TRUE and therefore your IF will be TRUE.

Something like this (with the correct variance used) should work for you:

=IF(AND(N2+O2<=E2+0.3,N2+O2>=E2-0.3),"Match","N/A")

Let us know if that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
January 31, 2017 at 21:01:08
Thank you. It worked GREAT!!!

I apologize for the confusion... it was a variance of .03 I forgot to double check my numbers.


Report •
Related Solutions


Ask Question