All language subtitles for 8. Exercise 01

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.