### IF(AND

Today I want to examine the often confusing If statement when using the Boolean AND.
=IF(AND
To the beginner Excel user, this can sometimes be confusing to understand.
Let’s look at a basic time sheet for a small company. All the employees get paid the same hourly rate of \$10 an hour but not every employee is entitled to overtime pay (anyone who works over 40 hours). And just for simplicity of the formula demonstration (and cause the boss is super cool), if an employee works even 1 hour over 40, they get time and a half for all the hours worked for the week! I call this company Fantasy Land and the boss’s name is Richie Rich.
 Employee Eligible For Overtime Hrs Worked Pay Rate Weekly Pay Bill Yes 45 10.00 675 Steve No 42 10.00 420 Dave No 38 10.00 380 Thomas Yes 40 10.00 400 Nancy Yes 52 10.00 780 Sue Yes 52 10.00 780 Rebecca Yes 38 10.00 380

If we look at the first employee, Bill is eligible for overtime and has worked 45 hours. So if we do the math, \$45 X \$15 (time and one half), he is owed \$675 for the weeks work.
Steve on the other hand has also worked over 40 hours but is not eligible for overtime. So his pay is \$42 X \$10 = \$420. It would be frustrating to have to manually calculate the weekly pay for every employee. Fortunately Excel allows you to factor multiple criteria with If statements that will help Richie Rich easily calculate his payroll expense.
 Employee Eligible For Overtime Hrs Worked Pay Rate Weekly Pay Formula in Column E Bill Yes 45 10.00 675 =IF(AND(B2="Yes",C2>40),(C2*(D2*1.5)),(C2*D2)) Steve No 42 10.00 420 =IF(AND(B3="Yes",C3>40),(C3*(D3*1.5)),(C3*D3)) Dave No 38 10.00 380 =IF(AND(B4="Yes",C4>40),(C4*(D4*1.5)),(C4*D4)) Thomas Yes 40 10.00 400 =IF(AND(B5="Yes",C5>40),(C5*(D5*1.5)),(C5*D5)) Nancy Yes 52 10.00 780 =IF(AND(B6="Yes",C6>40),(C6*(D6*1.5)),(C6*D6)) Sue Yes 52 10.00 780 =IF(AND(B7="Yes",C7>40),(C7*(D7*1.5)),(C7*D7)) Rebecca Yes 38 10.00 380 =IF(AND(B8="Yes",C8>40),(C8*(D8*1.5)),(C8*D8))

If we write out the logic it would look like this…
If an employee is eligible for overtime (YES) AND Hours worked > 40, multiply Hours worked X (Hourly Pay Rate X 1.5) ELSE multiply Hours Worked X Hourly Pay Rate.
The Boolean logic for writing this would be as follows…
=IF(AND(First Test),(Second Test) are both TRUE, Execute overtime pay calculation, Else Execute regular pay calculation)
So using the Boolean AND in our test for the If statement helps up easily join multiple evaluations to help us determine the correct answer.