### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

### Video Transcription

00:00
So in this exercise we have, ah, results off semifinals
00:06
men's 100 meter race competition.
00:11
And there were three races, three heats
00:13
and in each there were eight competitors, and the rules for qualifying into finals are
00:20
that 1st 2 in each race get qualified by position
00:27
and out off remaining
00:30
18 competitors. That's court in their races between third and eighth place
00:39
two will be selected based on best results. So, out of these 16 we have to pick two with best
00:46
results, meaning the lowest ones.
00:49
And they will also get qualified to the
00:53
race,
00:54
do the final race. Now, um,
00:58
we have these results
01:00
and we're
01:02
expecting that they are different,
01:04
all of them within one race. So it is possible that two competitors to two athletes will have the same result. But then the photo finish will decide who is
01:17
better out of these two.
01:19
So, um,
01:19
um,
01:22
we're is going to assume that they are
01:25
all different, because if they were all the same, then the referees would correct the rink manually. So we're going to do it just like they're different
01:34
and, uh, so, so determined ranking.
01:38
We have to look a tw three groups off eight competitors. So this is the first race, second race and third race
01:49
and look a TTE
01:52
their results for that. We're going to use if and large functions combined together. So if you remember large functions, looks at the range
02:01
and returns the
02:04
value that is on the end position,
02:07
10th position and number and his second perimeter in the function definition. So let's see how it works. So you were going to say, If
02:19
yeah, if
02:21
and then
02:23
so the result of this competitors
02:25
if it's equal to large
02:30
off
02:31
this range here.
02:35
So if three to F 10
02:38
it's all right.
02:40
And, ah, we want to find who's number one. So we say
02:46
it's the eighth position because the lowest is the better best.
02:52
And it's always,
02:53
uh, large is always
02:55
looking in the order higher, highest to lowest
03:01
because it's large.
03:04
And in that case, if that's true,
03:07
we're going to put here rank number one.
03:10
If not, we're going to continue with this and we're going to say if and then
03:16
again, if
03:19
this cell here
03:22
so again F three equals
03:27
now we're going just to do some corrections. We have to put a dollar sign here because we're going to copy this election and we want throws to remain a changed,
03:37
and then we're going to
03:42
just copy D's.
03:46
So if after e equals
03:49
sorry,
03:52
large off that and now we're looking
03:57
at the Position seven
04:02
and then the result will be, too.
04:10
We're going to
04:14
If if that is not the case, were again starting with. If so, we're going to copy all off this
04:21
because it's going to stay the same.
04:26
So
04:28
if large and then just now the position is going to be six.
04:32
And if that's the case, is going to be number three
04:39
and then again if
04:43
and is going to be five.
04:46
I'm sorry, four.
04:48
Then again if
04:51
and it's going to be six
04:56
three
04:58
and again if
05:01
and it's going to be
05:04
seven and two
05:08
and now no more ifs, because if it's not 1234567 57 position,
05:16
then it's going to be sorry we made a mistake and typing. So it's 123
05:23
four
05:26
and we have those. We were here eight and it's 17 It's too
05:31
sixes, 35 is four, and then here he should have Bean four and five
05:41
on. Then it should be
05:46
three.
05:50
And it should be like this and to
05:57
no, sorry if it's going to be four and then five.
06:01
Yeah, 2345
06:06
So
06:08
it's going to be six
06:11
and then
06:13
the last one.
06:15
So
06:17
if it's going to be
06:20
two,
06:23
then it's going to be seven and then Indy and eight. So you see, this is complicated. You can make a mistake typing this, so you should put it on paper first.
06:32
Before you,
06:36
It's now we just close up all the brackets and it's very nice that Excel will give us
06:42
color coding off these brackets. So we finished with the black one and re presenter, and now we got
06:49
ranking of this one
06:51
just expanded here, and we got here and to know that were correct to to check If we're correct, we'll get 12345678 So all of them will be there.
07:04
And now we're going to just copy this for
07:10
this one. It's not just not going to be 3 to 10
07:15
it's going to be 11 2
07:20
18
07:26
and we're going to select this
07:30
and just compete all over
07:33
everything, Gale sustained. Same.
07:43
You hear? A swell.
07:46
You're a swell
07:50
hand
07:51
here. A swell.
07:54
So just to check every 11 18 all the way through.
08:01
So where is the stake?
08:05
Yep.
08:09
So
08:11
this is it.
08:13
And then we're going to expand this for all these eight. Again. We have 12345678
08:20
And then Ridge is going to copy this
08:24
and corrected. So it's now going to be 19
08:30
two,
08:31
26
08:33
and we just have to do some copying.
08:37
So,
08:39
second,
08:41
third
08:46
sport,
08:48
if
08:50
six
08:52
and seven occurrence of this
08:56
presenter,
08:58
we expand this. And now we got rankings for each race.
09:03
So let's continue in next video.

### Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

### Instructed By

Milan Cetic
IT Security Consultant
Instructor