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.

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

plusyour variance and "Greater Than or Equal To" E2minusyour 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

Thank you. It worked GREAT!!! I apologize for the confusion... it was a variance of .03 I forgot to double check my numbers.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History