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.