All language subtitles for 3. Exercise 10

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.