Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.