For this lesson, you need to open the sample file: Earnings Summary-1, from your Sample Files folder.

This lesson introduces the IF logic function. The IF function is one of Excel's most widely used and useful functions.

We're going to use it to calculate the standard pay, overtime pay and bonus, in line with the Payroll Rules that are listed in rows 4, 5 and 6.

The rules are that all hours up to 35 hours per week are paid at the hourly rate. Hours over 35 hours per week are paid at time and a half. That's one and a half times the hourly rate. And if the employee has sales that are above target, then the sales that are above target will have a 5% bonus.

So let's start by calculating the standard pay. I'll click, in cell: F9. And I'll insert an IF logic function, into this cell. That is: Formulas, on the Ribbon. In the Function Library, the Logical group. And in the Logical group, there's the IF function.

The IF function requires a Logical_test. And then, performs one action if the test returns TRUE, and a different action if the test returns FALSE.

Let's consider the hours worked by Jack Nicholson. He's worked for 30 hours, so he'll be paid all of those 30 hours at standard rate.

But now, let's consider Brad Cruise. He's worked for 40 hours. That's more than 35 hours, so he'll be paid for 35 hours at his standard rate, and for 5 hours, at his overtime rate.

So the Logical_test is going to be whether the employee worked for more than 35 hours. So I'll click, in cell: E9 (hours worked), and then, I'll type: <=35

And if that's TRUE, then the employee worked for less than 35 hours. So I'll click in Value_if_true, and in the case where the employee's worked less than 35 hours, he'll be paid for the hours he did work, at the hourly rate. So I'll click on the Hours worked, and simply multiply them by the hourly rate.

And now, let's consider what happens if the value is FALSE. In other words, if the employee worked for more than 35 hours. Well, in that case, they'll be paid for 35 hours at standard rate and the excess at overtime rate. So they'll always earn 35 hours, multiplied by the hourly rate.

I'll now click: OK, and I'll auto fill, to the end of the range. And let's audit a couple of those figures.

First of all, Jack Nicholson. He's worked 30 hours at an hourly rate of $13.00. So 390 is correct. That's 30 times 13.

And now, let's look at Brad Cruise. He's worked 40 hours, so 35 of those hours should be at standard rate. 35 times 15 is, indeed, 525. So the IF function has worked correctly for standard pay.

Let's now move onto overtime pay. I'll click, in cell: G9, and once again, insert an IF function. That's: Formulas, on the Ribbon: Function Library: Logical, and the IF function.

The Logical_test. Well, that's going to be exactly the same as last time, because only if the employee has worked for more than 35 hours will they receive any overtime pay. So the Logical_test will be the number of hours worked is less than or equal to 35.

And you can see that in the case of Brad Cruise this is returning FALSE, because Brad did work for more than 35 hours. But if this value was TRUE, and the employee had worked for less than 35 hours, well then he wouldn't get any overtime pay. Because he hasn't worked any overtime. So 0 would be the correct result.

But if this is FALSE and the employee has worked for more than 35 hours, then the answer is going to be: (I'll open a bracket), the hours that they did work, minus the 35 hours that they've already been paid at standard rate. And I'll close the bracket, multiplied by the hourly rate (it's 15), multiplied by 150% (that's one and a half times the normal pay for the overtime).

I'll click: OK, and I'll auto fill to the end of the range. And notice that only the employees that have worked for more than 35 hours are getting any overtime pay.

And let's audit one of those figures. The figure for Brad Cruise. He's worked for 5 hours over 35, so the overtime pay should be 5 times 15 (which is 75), plus half, again. Which is, indeed, 112.50. So I can see that overtime pay is also being correctly calculated.

And now, let's move onto bonus. So I'll click, in cell: H9. Once again: Formulas: Function Library: Logical, and the IF function. And let's think about the Logical_test this time.

Well, employees only get bonus if their sales are above target. So the Logical_test will be: Sales are greater than Target.

Now, in the case of Brad, that's returned TRUE. And it's returned TRUE because his sales of 22,000 are above his target of 10,000.

So if this is TRUE, the employee is going to receive 5% of the sales that are in excess of the target. So, first of all, I'll open a bracket. And I'll click on his sales, and I will subtract the target. And this gives me the sales above target. And on his sales above target, he's going to receive a 5% bonus. So multiplied by 5%.

Now, if the value's FALSE, it means that the employee's sales are below target. And if they're below target, they don't get any bonus. So the answer here is: 0

I'll now click: OK, and I'll auto fill, to the end of the range. And notice that only the employees whose sales are above target are getting any bonus.

Let's audit one of those figures. Well, in the case of Brad Cruise, his sales of 22,000 are 12,000 above the target of 10,000. And 5% of 12,000 is, indeed, 600. So the bonuses are being calculated correctly, as well.

Let's now work out the total pay for the week. Well, it will be the simple sum of the cells in columns F, G and H. So I'll click in: I9, type: =SU, to begin the function. Down Arrow key twice, to the SUM, Tab key, to start the function.

This time, I think I'll use the mouse to drag across those three cells. And when I press the Enter key, I can see that Brad Cruise earned $1,237.50 during this week.

I'll auto fill to the end of the range, now. Let's do it a different way. I'll click on cell: I9, and double-click on the AutoFill handle. And I can now see how much each employee is receiving for their week's work.

All that remains, for this lesson, is to save your work. And I'm going to save with the new name: Earnings Summary-2. And, as usual, I'll save to the folder above my Sample Files folder.

I click the Save button, and you've now completed Lesson 3-5: Use the IF logic function.