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
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.