All language subtitles for 8. Exercise 01

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

It's time now to do exercise one.

And in this exercise, we're going to practice some of the skills that we've learned in this section

on logical functions.

So in the first part of this exercise, I'd like you to use either nested f's or the if s function and

complete the prize money field in the table and you'll notice on the right.

We have a smaller table which contains the information that we're looking for.

And remember, if you're going to copy this formula down, then you need to make sure that you're looking

at those cell references in place.

In the second part of this exercise, we have a similar table, but this time I'd like you to practice

using count if some efforts count.

If and some ifs are not to use those formulas and complete the results in Column H, so it's time to

pause this video and give that a go.

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

So in this exercise, we need to use nested ifs or the F as function to work out the prize money.

So I'm going to use if s to do this.

So we want a first logical test.

Well, our logical test is if the medal is equal to bronze f four to lock that cell reference.

If that's true, then they're going to get $100 in prize money f for to lock comma.

Because I'm using F s, I can go straight into logical test number two.

If the value in Cell D four is equal to silver, then they're going to get $250 in prize money.

And the last logical test is if the value in D four is equal to gold, then they're going to get $500

in prize money close of the bracket hit enter and now we can copy this down.

And if we do a quick spot check, let's take a look.

Every time we have gold, we have five hundred bronze, we have 100 and silver, we have two hundred

and fifty.

Let's take a look at the second part of the exercise.

Well, here we need to use count ifs and some ifs.

So in the first table here, we need to find the number of athletes from the USA.

Now we only have one piece of criteria.

So for this, I'm going to use count if my criteria is going to be USA.

Where am I going to find that I'm going to find it in the country?

Column control shift down Arrow.

I'm going to work on this up in the formula bar and my criteria because I don't have it contained within

a cell.

I'm going to need to type that in.

So let's put USA in quote marks.

Close the bracket and enter the next one.

We have two pieces of criteria, so we need to use count ifs.

So our first piece of criteria is going to be that the athlete is from Australia.

So our criteria range number one is going to be the country column control shift down Arrow.

Let's work on this up in the formula bar.

Our first piece of criteria is Australia Criteria Range two, while our second piece of criteria is

female.

So our criteria range is going to be the gender column comma and our criteria is going to be f close

the bracket hit enter and there I get my result.

And finally, we have three pieces of criteria here.

So count if again, we're looking for athletes from the UK who are male, who achieved a silver medal.

So three pieces of criteria.

Our first criteria range is the country control shift down arrow.

Up in the Formula Bar once again.

And criteria number one is UK criteria range two is the gender.

So let's select that control shift down arrow.

And our criteria this time is male and a third criteria range is the colour of their medals.

So let's select that.

And this time we are looking for silver medals.

Close the bracket, hit enter and there we get the result.

And finally, I asked you to work out the total prize money for various different pieces of criteria.

So for this, we're going to use the sum if function for the first one, we just need some f because

we only have one piece of criteria.

Our criteria is the USA.

So our range is going to be the country control shift down arrow and then we need to put our criteria

in quote marks.

And then as some range is going to be the prize money control shift down, Arrow closed that bracket

and enter.

Now we need to do some ifs because we have multiple pieces of criteria.

This time we have the some range, first of all, so we're going to sum the prize money once again.

Criteria range one is going to be the gender and the gender we're looking for this time is female.

And then a final piece of criteria is that we're looking for Canadian athletes only.

So we want our criteria range to be the country and our criteria is going to be Canada.

Now, the final one was a little bit unfair of me, really, because I didn't actually show you this

in the lesson, but I wanted to show you this in the exercise just so you can see something else that

you can do with some ifs.

Now for this one, we want to total the prize money for North America, so that's going to be the USA

and Canada.

So we have two pieces of criteria that are coming from the same column.

So how would we do this?

Well, if you worked this out, well done.

It's not too hard to work out.

But what we need to do here is basically perform to some miscalculations.

So let's start out with our sum if we want our range, so our criteria is going to be USA.

First of all, so our range is going to be the country range.

And our criteria is going to be USA and we want to specify as some range which is going to be the prize

money.

Now what we need to do here is close off the bracket and then say that we want to plus and then go straight

into the next sum.

If so, again, our range is going to be the country.

But this time the criteria is Canada.

And then finally, the sum range is the prize money and close the bracket, so we're just doing to some

ifs and we're adding them together so that we can get the total for two pieces of criteria that are

coming from the same column.

Let's say enter.

And that is our answer.

So hopefully you got most of those.

I wouldn't expect you to get the last one, but if you did, very well done, I will see you in the

next section.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.