Alex is still concerned that there may be some errors in the account manager focus

section, which contains some of the more complex formulas.

So he's going to come in and just double check that.

Now generally the best way to avoid errors in your formulas is to

check errors at the time of creation.

Because we've seen that it's quite hard to go and find them and correct them

after the fact, but unfortunately we can't always rely on people to do that.

So it may be that we have to look at errors afterwards.

So what Alex is going to do is he's going to come across and

make sure that this all looks like it's working well.

Now what we've got is a little calculation here to work out the total orders for

the selected account manager, and these are then ranked.

So if they get less than 20, it's poor.

20 to 50 is good, etcetera.

And this seems to be working fine.

But where you have a formula that's doing something quite complex,

it's a very good idea to check a couple of values.

So we're going to come here to where it says Connor Betts,

click the dropdown menu, and select Phoebe.

And dear, there is an error, and

the problem is this formula wasn't checked for all the alternatives.

Fortunately, we have detected it, so let's go and have a look at our calculation.

Okay, so what we have here is a nested IF function.

And you can see it's checking if it's less than 20, give them poor.

If it's less than 50, give them medium, etcetera.

Now you might have already spotted the problem, but

in a big calculation like this it can be quite difficult.

And so Excel gives us another great tool to help us actually work

through the formula step by step and identify where it's going wrong.

To use it, I'm going to press Escape, so I'm just selected on

the cell with the formula, but not actually viewing the formula.

And I'm going to come up to my Formulas tab, and

in my formula auditing group I'm going to click Evaluate Formula.

And this dialog will allow us to work through the formula step by step and

see what Excel is actually doing and where it's going wrong.

So we're going to start by clicking Evaluate.

And what it has done here is it has resolved the cell references.

It's checking if the value in B30, which is 87, is less than 20.

So whatever is underlined, that's going to be the next step to be performed.

And when we clicked on that it says 87 is not less than 20.

So it's going to jump to the next step.

It's now going to check if 87 is less than 50.

And there we go, this too has come back false.

So now it's going to check if 87 is less than 100.

And yes, that's the case, so this time it's going to return true.

And dear, that is where our problem was.

When it came to true, and it tried to resolve the text Good, it couldn't.

You've got a name error here.

And we often get the name error because we've got missing quotes, and

this is exactly the problem here.

Now if you want to keep going through the steps,

you can actually go to the end and then just start all over again.

And once you're happy, you can just click Close.

So let's go and fix that error.

So we're just going to double click, and

around the Good we're going to put our double quotes.

And then press Enter.

And that has now been fixed.

It's such a small error, but it can create big problems.

All right, that's one problem solved.

Now we're just going to double check if our results are coming back

nicely as well.

And as you can see here it's returning Phoebe's total sales for the year 2015.

And we can change the year.

So this is quite good.

We can change to 2014 or whatever, and it updates the total sales.

And that looks like it's working fine, but it's always worth double checking.

So let's come and have a look at Phoebe's sales for 2015.

So Phoebe's employee ID is E1250, and

here are her sales for 2015, and they were, dear.

Now that doesn't look right.

It's actually bringing back the 2016 sales.

So we still have a problem here.

Let's go and have a look at the formula.

So when I double click we see that it's an index match.

Now you already know about index matches and

you may have already spotted the problem.

But again, in a big chunky formula like this it can be really tricky.

So let's try and do our Evaluate Formula again and see if it can help us.

I'm going to press Escape, come back to Evaluate Formula and

we are going to go through step by step.

Now the first thing it's going to do is do our first match.

So it's going to try and find out which row this employee ID is.

So we're going to click the Evaluate and it's going to do the match.

It will try to find Phoebe Gour.

And it's done the match and that didn't seem to cause any problems.

Excellent.

It's founded in row 14.

So that's worked beautifully.

It's now going to do the second match to try and find the year 2015.

And we're going to click Evaluate and evaluate again.

And it has found it in column four.

Now let's double check that.

So we have one, two, three, four.

Dear, that is where our problem is.

It should be finding it in column three.

And that's why we've got an issue.

So once again this has helped us narrow down the problem.

Okay, let's go and work out how to fix it.

So we'll click close here.

Come back to our calculation.

And if you have a look at the second match, it's looking from C4 to G4.

So its actually starting at the wrong point.

It should be starting at D4.

So that's going to be a pretty easy fix for us.

We're just going to click in there and make that D4, and press Enter.

Problem solved.

Another little handy tool that's available to us for

doing formula auditing is the Watch Window, and I will show you this now.

The Watch Window can be found on the Formulas tab, so come up here.

And it allows us to watch a cell even when we're not

on the same worksheet as the one that contains the cell.

So for example, we have here our projected increase percentage shown in H3.

That's 7%.

When we change this value it is going to impact the projections calculated

in the projections sheet which we can't see from here.

We would like to see how changes to that cell impact our projected profit,

but this means switching between the two worksheets and we don't want to do this.

So we're going to click here into E8.

And we're going to come up to Watch Window.

And this will open a new dialogue.

At the moment we have no watches, but

when we click the Add Watch button it will add the cell we previously selected.

And then we just click add again.

We can now see the cell reference, the value it currently contains,

and even the formula being used.

What's handy, though,

is that when we come back to our sales dash we can still see the Watch Window.

And now when I come and change my projected increase to 8%, for

example, the Watch Window shows us the change to our projected profit.

So the Watch Window allows us to keep watch on one or several cells,

even if we can´t actually see them when we´re making our changes.

So it´s a really handy little tool.

To clear a watch, just select the watch and then click Delete.

It will remove it.

And to close the Watch Window, just hit the cross at the top.

So in this video we saw how we could take more complex formulas and

either find out where they're going wrong or

maybe just work out how they're actually working using our Evaluate Formula.

Alex has now corrected all the errors in this workbook and it's looking fantastic.

Make sure you save your work.

And in the next video we're going to look

at how we can protect our workbook so

that our formulas don't get broken again.

[SOUND]

Practice Video: Evaluate Formula, Watch Window

Excel Skills for Business: Intermediate II

Conheça os instrutores