Before we start this lesson, I need to cover the concept of precedence.

This is how I teach precedence in my classes. I put up a slide and I ask the class the answer to the following sum.

The class always agrees that 6+(2*3)=12.

I then ask them: what about (6+2)*3? And the class, nearly always, agree that the correct answer is 24.

But, nearly always, I manage to divide opinion upon the answer to: 6+2*3. Half of the class usually goes for 24, and the other half for 12 but, mostly, they're guessing.

Bearing in mind that most of my classes are taught to office workers, who work with figures every day of their lives, I'm confident in my assertion, that the rules of precedence are a mystery to at least half of the adults who work with Excel for a living.

But I'm sure you knew that, because of the rules of precedence, 6+2*3=12. And that's because multiplication has precedence over addition. In other words, Excel does all of the multiplication before it does the addition.

Now, we'll look at Excel's precedence rules, along with a mnemonic that helps to remember them. First of all, any expression in brackets (or Parenthesis), is always evaluated first.

Exponents (^) are always evaluated next. Exponents tend to be used in engineering or scientific scenarios, and are rarely seen in accounting scenarios.

Next, in the order of precedence, are the Multiply (*) and Divide (/) operators. These are evaluated from left to right. When a formula only consists of multiplication and division operators, you don't have to consider precedence.

And last, in the order of precedence, are the Addition (+) and Subtraction (-) operators. These are also evaluated from left to right. When a formula only consists of addition and subtraction operators, again, you don't have to consider precedence.

If you only work with accounting scenarios, all you really need to remember is: brackets (()) are evaluated first; multiplication (*) and division (/) are evaluated next; and addition (+) and subtraction (-) are evaluated last. Now we've covered that, let's get back to Excel and get on with the lesson. For this lesson, you need to open the sample file: Payroll-1, from your Sample Files folder.

This worksheet contains some simple formulas, required to compute Net pay from hours worked.

You can see the Payroll rules, in cells: A3 to B7. Most tax regimes have more complicated rules than those defined in this simple example.

Employees are paid the same hourly rate for all hours worked. And a different percentage of gross pay is then deducted for tax, social security and pension contributions.

So that you can see the whole worksheet, I think I'll shrink the Ribbon. So I double-click on any of the Ribbon tabs, and the Ribbon shrinks. You can now see the whole worksheet.

Let's look at the formula, in cell: B17. I'll click, on cell: B17, and you can see on the Formula Bar that it's: B15*B4.

There's really not much that can go wrong with such a simple formula. But let's audit it anyway, using Excel's Evaluation feature.

So I click, on cell: B17, and then: Formulas, on the Ribbon. And in Formula Auditing: Evaluate Formula. And the Evaluate Formula dialog has appeared.

Notice that I'm seeing the formula with an underline, underneath cell: B15. That means B15 will be evaluated first.

Now, I can see from looking at the worksheet that B15 is a total of all the hours worked in the week. And it's possible that there could be an error in the formula, in cell: B15. So I'm going to click the Step In button, to see the formula behind the value.

So I click: Step In, and I can see that the formula is the sum of the range: B10 to B14.

Now, I'm going to click the Evaluate button. And because SUM(B10:B14) has an underline underneath it, that will be evaluated next.

I click: Evaluate, and I can see the answer of 46.

Now, I'm going to click: Step Out, to return to the main formula. And you can see that the value of 46 has now replaced the first item in the formula. And there's an underline, under B4. That means the next thing that will happen, is that the value in B4 will be brought back.

I can see that the value in B4 is the hourly rate of 12. So I click: Evaluate, and indeed, 12 is brought back.

The next thing Excel's going to do is perform the Multiplication, which is: 46*12. So I click: Evaluate, and you can see the answer that's currently in cell: B17, of 552.

Well, that was a very simple example, but what about a more complicated formula? I'm going to click in cell: B23, where there is such a formula.

I've deliberately calculated Net pay in a rather long-winded way. And also, I haven't used any parentheses, so that you can see that this sort of formula could very easily produce the wrong result. Because I've made an incorrect assumption about Precedence.

Actually, it's returning the correct result, but let's audit it anyway, and see how the rules of Precedence are being applied by Excel. So I click: Formulas, on the Ribbon. And in Formula Auditing: Evaluate Formula.

Well, I can see the first thing that Excel's going to do, is bring back the value in cell: B15.

I could click: Step In, as I did before, but I think I'll just evaluate this time. Because I know the formula in B15 is correct. So I click: Evaluate, and the value 46 is brought back.

Next, the value in cell B4 is brought back, when I click: Evaluate. So I'll click, and there's the value of 12.

And now, Excel's going to perform the Multiplication of 46*12. So I click: Evaluate, and the answer is 552.

Things get a bit more interesting now because, as you know, the Precedence rules are that Multiplication must take place before Subtraction. So Excel can't deduct the value of B15 from 552. It must first multiply it by B4 and B5.

Let's see it doing that. I click: Evaluate, and 46 is brought back.

Now, the contents of cell B4, Evaluate, is 12. And now, the first Multiplication: 46*12, is 552. And now, we bring back the contents of cell: B5. That would be 0.32, and we multiply that by 552.

Now that all the Multiplications are out of the way, it's quite okay for Excel to do the Subtraction. So 176.64 are going to be subtracted from 552. I click: Evaluate, and the Subtraction is performed.

And now, we have a very similar situation. We must perform the three Multiplications, before it's okay to perform the Subtraction, because of the rules of Precedence.

So bring back: B4, bring back: B15, multiply.

Bring back: B6, and multiply.

And only now can we perform the Subtraction: 375.36 - 44.16. I click: Evaluate, and the Subtraction is performed.

And, finally, exactly the same thing for the third time. We must first multiply B4 by B15, by B7, before we can subtract. Bring back: B4, bring back: B15, multiply.

Bring back: B7, multiply.

And now, it's fine to perform the Subtraction. So I evaluate, and there's my result: 303.60.

Let's do that just once more, but a little bit faster, this time. So I'll click: Restart, bring back: B15, bring back: B4, multiply. Bring back: B15, bring back: B4, multiply. Bring back: B5, multiply. Subtract. Bring back: B4, bring back: B15, multiply. Bring back: B6, multiply.

And now, I can subtract. Bring back: B4, bring back: B15, multiply. Bring back: B7, multiply. And only now can I subtract.

Let's now close the dialog, and I'm sure you can appreciate the value of the Evaluate Formula feature, when you have a value that you suspect may be incorrect. And you want to audit the way in which Excel is arriving at the correct answer.

I'd actually never create a formula like this. And that's because I use parentheses even when they're not needed. I'd write the formula you can see in the Formula Bar, like this.

The parentheses are not needed, because the Precedence rules state that Multiplication happens before Subtraction. But I still prefer the formula with the redundant parentheses.

Well, all that remains, for this lesson, is to save your work. And I'm going to save with the new name: Payroll-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-1: Understand Precedence rules and use the Evaluate feature.