All language subtitles for 3. Exercise 10

In this exercise, I'd like you to practice some of the skills that you've learned in this section on

formula auditing.

So the first thing I'd like you to do is just to fix the errors in the spreadsheet.

So the top here, we have four little tables and you can see that we're getting some name errors and

also an error.

So I'd like you to take a look at these errors, see what's going wrong and see if you can fix them.

Next, I'd like you to create a V lookup formula that returns the product and price based on the order

number from the fruitless table.

So I'm talking about these little headings down here.

We have some order numbers and we have a product column and a price column, the currently blank.

So I'd like you to create a V lookup formula that looks at the data on the fruit list worksheet and

again, just a very small data table and returns the product and the price.

And if when you're doing this, you happen to get some kind of error.

I'd like you to trace the errors to see which part of the formula is producing the error.

Once you've established that, I'd like you to add in some error handling to the formula.

So the message says something more meaningful other than and a now is the time to pause the video and

give that a go.

If you'd like to see my answer, then please keep watching.

So the first thing we need to do here is fix the issues in this spreadsheet.

So if we take a look at this first little table, we have a name error.

Now, if I click on here and take a look at the formula.

Notice here that I have accidentally spell the name of the formula wrong.

I've got average x instead of average.

Now, if I wasn't sure if I got the name of the formula wrong, remember I could always use my error

checking utilities on the formulas ribbon.

So if I click on Evaluate Formula and step into this formula or evaluate this formula, it's showing

me exactly where the error lies in this formula.

So it's replaced average X with the name error.

So I know the issue lies with this part of the formula and not the sell references, so I'm going to

go into my formula bar.

Let's just remove the X enter and now my formula is working.

Let's take a look at this next one.

Let's click on the cell where we have the name Arrow.

And if I take a look in the formula bar, I can see it's doing a sum of a named range.

Now, do I have any named ranges in this workbook?

Let's click the dropdown and see.

Well, yes, I do.

I have one called fruit list, but that relates to the table that we have on the fruit list worksheet.

So the named range that I'm using to perform this, some calculation isn't actually valid.

Now there's a couple of things I could do here.

I could replace the named range with the actual cell references, or I could quickly create a named

range called sales.

So that's what I'm going to do.

I'm going to select all of these cells into the name box.

Let's call them sales enter and straight away my formula updates.

Now, the next one is an a error.

No.

If I click on this and take a look up in the formula bar, I could see that we've got a v look up going

on here.

So what's it doing?

It's looking up what we have in a 15.

It's looking it up in this table and it's returning the price column and I'm getting an NRA.

Now, why is that?

Well, if we take a look at the look up value notice that I've spelt the word check in wrong, there's

an extra eye in there.

So the V lookup formula can't find the word chicken in this table.

Now there are a couple of things I could do here.

The most obvious one is just to remove the spelling error from the look up value, but I could also

add some error handling in here as well.

Now, I'm not going to do that because it's not really appropriate in this case because the word is

actually spelt wrong.

So let's double click.

Let's remove the I hit enter, and now that price is correct.

Now finally, the bottom here I asked you to create a V lookup formula that returns the product and

price based on the order number from the fruitless table.

So I'm going to click here.

Let's do equals v look up.

My lookup value is the order number 824 I'm looking at up in the fruit list table now.

I could jump across to the fruit this worksheet and select everything.

I've actually already named this range.

It's called fruit list and you'll see that it's updated in the Formula Fruit Underscored list.

So if you wanted to use the named range as opposed to selecting the sounds, you can definitely do that.

As well, Coma.

Now we need to specify the column, we want to return.

So we want to return the product, which is column number two, and we want to do an exact match of

the order number.

Close the bracket, enter and it's given me the product of grapes that's double clicked.

Copy that down.

And would you take a look at that?

I have an and a error.

Now we're going to deal with that in a moment.

Let's just do the price as well, sir.

Equals V look up.

Look up, value is the order number.

Table array is the fruit list.

I'm going to press the three key and select my name range from here because that's a bit easier this

time.

The column we want to pull back is the price, which is column number three in the fruit this table

and we're doing an exact match.

Close the bracket, enter.

And if we copy this down again, I'm getting an NDA just here.

And for the most part, this is probably going to be because this order number doesn't exist in the

table.

So if we jump across and take a quick look because we don't have many of them, I can say, nope, I

don't have order number one zero zero zero three.

And what I could do here is I could click on this error.

I could evaluate the formula.

And if I click Evaluate, it's going to go through each step of the formula.

And I can see that when it gets to the look up value part, that is where I'm getting the energy.

So it really helps me determine which part this formula is incorrect.

What I could also do here is trace presidents, and that's going to show me which cells I'm using in

my V lookup formula.

Now, in order to fix this, I'm going to add some error handling into my V lock up.

So let's go over to the formula bar because this isn't an error.

I can just wrap this in an if and a formula.

Let's click at the end comma and I'm going to say not found.

Close the bracket enter and then we can double click to copy that down and we can do exactly the same

for the price.

So, if any, again, we're just making this more meaningful.

Close the bracket, enter and then we can double click to copy that down.