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

And let's start with, uh, visual basic

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.

### Up Next

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

Instructor