### Intermediate Excel

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

### Video Transcription

00:00
Okay, Now we have to determine the status off
00:05
each athlete. So have they qualified or not?
00:09
And it's very simple if they were first or second within their group. So it can be simple if
00:17
and if it's one or two. Or let's say if this cell is here smaller than three because we're talking about integers, then it's qualified. So we're going to put here. Q.
00:29
But if if it's not, we're going to create the function, and this function is going to look for all the others
00:38
that they're not qualified by position
00:41
and then it's going to determine
00:45
to off them that have the best result meaning the lowest time.
00:51
00:56
and let's
00:58
create.
01:00
Ah,
01:02
sorry it's here.
01:03
So
01:04
let's insert on module.
01:08
And, uh,
01:11
let's ah, define it as a function.
01:19
And this function is going to be called
01:23
competitors status.
01:27
And, uh,
01:30
we're here, put a result as variable, so we're goingto put in that function. Result of that competitors and the function is going to determine
01:42
is it is the competitive, qualified or not,
01:45
and we have to define the type of this, uh, function. So it's going to be a string.
01:52
Why? Because
01:53
we want,
01:56
uh, What we're going to return is Q or an Q so qualified are not qualified,
02:05
and we first have to define some things. So
02:09
we're going to define some variables 1st 1 is going to be
02:15
none qualified time, and it's going to be an airy
02:21
So we have 24 competitors. Six have qualified, so we have
02:24
18 remaining
02:28
that have not qualified.
02:31
So it's going to be done like this. So even to say one to Il 18. So it has 18.
02:38
It's ah, it's a single dimensional array and it has 18
02:43
position cells
02:45
and, of course, as single because we're talking about times there, so it has to be decimal numbers single.
02:53
Then we're going to define some counters
02:58
a as sorry
03:00
I as
03:01
in de Germ,
03:07
and also we have. I want this
03:10
into Tre,
03:13
and if we start needing something on the way,
03:16
we're going to
03:19
put them later.
03:21
So first of all, we're going to go through all 24 results
03:28
and look, if
03:30
value
03:31
in ah in ah
03:35
ah ranking is one or two.
03:40
So if it's not, if it's higher than two. Then we're going toe. Add the result in the
03:49
in disarray.
03:50
And if it's somebody who has qualified,
03:53
we're just just not going to do that. So
03:58
let's say like this. I won. We start with manual counters. Zero and then
04:04
we're going tow.
04:05
I have,
04:08
uh, loop for
04:11
sorry. We're going to go from 3 to 26
04:15
because these are the rose in original table
04:19
and then we're going to ask a question.
04:25
It is, if
04:27
range
04:29
off and then we're going to look at column G where the
04:33
ah rankings are
04:36
and we're going to add a row number, which is I.
04:42
So if the value off this is greater than two, that means that competitors have qualified. Then
04:50
we're going to do the following were first going toe add
04:56
one to the counter off.
05:00
Uh, the cells in this you ah, ray called and cute time.
05:06
And then we're going to say and
05:10
Q
05:11
time off. I won. So off that position, we put
05:16
range
05:19
and then
05:21
results are in column. If
05:24
and I
05:30
and don't well, you.
05:32
So we have done this one successfully,
05:36
and we're just going to close this and we're going to say next.
05:42
I
05:44
So now we have that to Ray.
05:46
Now we have to sort it
05:48
and for sorting, we're going to use the bow standard,
05:53
most inefficient but most but the simplest sorting algorithm because we don't really care about how fast is going to happen because we have only 18 values.
06:03
So we've been safe, for I equals 1 to 18
06:09
and then for I won equals 1 to 17. And what we're looking now
06:16
is, and we have a question. So if
06:19
and cue time
06:23
off
06:25
Ah,
06:27
I, um,
06:30
is larger than an Q
06:33
time off I one plus one.
06:41
And if it's the case then
06:44
and this is what's happening. So we're going to go
06:47
18 times because we have 18.
06:51
Ah,
06:53
remember, the cells are in the array,
06:57
and then we're going to check if the 1st 1
07:01
is larger than the next one. So the 2nd 1 and then the next pass is going to be 2nd 1 compared to 3rd 1 So if this one is larger,
07:12
then we're going toe, replace their positions.
07:15
So we're going to
07:18
He was
07:25
temp result,
07:27
and it's going to be cool and cute.
07:30
Sorry. Time
07:31
I won.
07:33
So it's a temporary
07:35
variable.
07:38
And then we go said, Thank your time off. I won
07:43
equals in cute time
07:46
Bluff.
07:47
I went plus one.
07:51
And then in cute time off
07:56
I one plus one
07:59
equals
08:00
temp
08:01
result
08:05
And, uh,
08:07
yeah, that's it. We have exchanged them. And if
08:13
and then here was going to be next
08:16
I won.
08:18
And then next I
08:22
So we have no sword to disarray successfully,
08:28
and now it's sorted. Now what? We have to see
08:33
if that's ah,
08:37
athlete has qualified or not. The one that we're asking this four.
08:43
So we have given its result. And now we're what? We're going to have a simple question.
08:48
So if result
08:50
is
08:52
smaller than en que time
08:58
sorry
09:00
off 16
09:01
it means if it's on a 17 or 18 position, remember again, we have sorted this in S O. The largest is first,
09:11
So it's descending order. So positions 17 and 18 they have the best time, so the lowest time. So we want to see if we're on position 17 or 18.
09:22
So if this is
09:26
the case, sorry,
09:31
then
09:33
yeah,
09:35
happens when you
09:37
type
09:39
that,
09:39
then,
09:41
uh, we're going to return comp
09:45
status. So now we're using the name off. The function is variable. So we're saying, OK, this function is now variable and this is what's going to be returned to excel so
09:58
and it's equal.
10:01
Q.
10:01
So if that is the case
10:05
that competitors has qualified
10:09
else
10:11
and it's for a simple
10:16
status
10:18
is going to be equal,
10:20
not qualified.
10:22
And we're going to close on Dhe if.
10:26
And that's it.
10:28
This is this function.
10:31
You're going to save it.
10:33
We're going to close
10:33
this one
10:35
going to close this one, and we're going to 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