# 2.4 Complex Formulas with Arrays

Video Activity

Join over 3 million cybersecurity professionals advancing their career

Sign up with

or

Already have an account? Sign In »

Video Transcription

00:01

Okay, Let us now continue with more complex formulas using a raise.

00:06

So

00:08

always go their record to the example.

00:11

And here we have, uh,

00:14

list off products that one companies selling

00:17

just

00:18

lying Lawson waas

00:20

out of many.

00:22

And because it's been sold by field says for us, they can negotiate the price. And we have here for the each sales the value that was it was sold for for each item, and they are entered as they are salt. So whenever the sales person closes the deal,

00:42

they get information to the head office and they put it in excel sheet.

00:48

And what we want to determine is what was the best

00:53

sales price achieved for each item.

00:58

And because thes date, this data has entered completely without any order,

01:04

we cannot just use max function, which will just look att the maximum value off the price. Because some of these air for wine, glass, some of these are for us.

01:15

So what we're going to do is we're going to go by and Max and if

01:21

and a raise,

01:23

and we're going to say, okay, we're looking for a maximum off,

01:27

and then we have a condition if

01:32

and then

01:33

if these

01:34

are equal

01:37

to wine glass,

01:40

then

01:41

we go here

01:42

and pick the

01:46

price.

01:48

And if it's not, we're just going to help. False

01:52

or we can put zero

01:55

and then we're going to close. If and then we're going to close. Max

02:00

and, of course, control shift. Enter even this 29 which for wine glass. When you look a TTE, this small sample of data is the correct one.

02:09

We can do the same thing for us. So we're going to go,

02:14

Max.

02:15

And then if

02:19

and then again,

02:21

product name should be equal

02:23

two.

02:24

He too, which is Woz.

02:28

And then, in case that is true, we're going to look at the field, which is

02:34

cold. Aye, or selling. Call me

02:37

otherwise it's going to be false or zero

02:39

closing GIF closing Max

02:43

Patrol shift enter

02:45

and we're getting 39.5.

02:47

So it's a very simple way howto by two functions and the rays and to the things that you would have to do in rather complicated way. So one of the

03:00

ways would be to have here command function if

03:06

and then again, we say, if this

03:09

is equal,

03:12

do the wine glass.

03:14

Sorry,

03:15

this is

03:16

equal to the wine glass.

03:20

Then

03:22

we pick

03:23

this one.

03:25

Otherwise it is zero

03:30

it close it

03:34

and then we expand this through entire range.

03:40

And we, uh

03:50

we made a mistake.

03:54

So

03:57

let's do it again.

04:01

So the alternative would be

04:04

without using Get Reyes is to have intermediary cones. And this one would be, for example, if

04:13

and then

04:16

this item is equal

04:20

to this item

04:24

and we're going to make it with strings

04:28

so that it doesn't change when we

04:33

open the cell, then is going to be this otherwise is going to be zero.

04:41

So we're going to close it

04:44

and then we get this, we expand this

04:47

and then we can make the max out off

04:54

this range here

04:57

and we get again the 29 which would be the correct calculation. So you see how complicated this is Also, if we would

05:06

ah be looking for, for example, minimum price, then instead of zero in dysfunction, we would have to put a very large number. Here is just to make sure that it is larger than anybody else. Anybody, any, any other price here

05:21

because then we would use the

05:25

the minimum function here and zero would be definitely the smallest one. So we can put zero. We have to put some very large number,

05:33

so this would complicate things.

05:35

And the this shows you how complex

05:41

the functions or formulas you can use a raise to great benefits.

05:48

Now, let's move to next example.

05:55

Which would be this. So we have this Ah, list.

05:59

And, uh, this is, uh,

06:01

the NATO alphabets, which is used when you have to convey a message, and the line is very bad. So instead of a you say, Al finds the V, you say Bravo.

06:14

And, um,

06:15

you want

06:17

just the type here, a letter here

06:20

in cell C one and you want in d one to get the equivalent spelling off the letter.

06:30

So the classical or traditional way to do this would be two years. We look up Calmund or function,

06:38

and we would, uh,

06:41

look for this

06:43

in Ah,

06:45

this range here.

06:47

And three answer would be from in column B, which is second column in the selection,

06:55

and we can just close this

06:59

and we will get off. So if we type here B,

07:02

you get Robbo and so on and so on.

07:05

The problem with this is that we need to have the stable somewhere,

07:11

so there is another way to do it. So let's say that our reference letter will be

07:16

cone

07:17

you in. So Hee won

07:20

and we want to do just with having one function. We're also going to go with Libby. Look up.

07:30

But we're going to look at this inn.

07:33

That array constant, Which looks like this.

07:38

A

07:39

coma

07:40

house, huh?

07:43

Semi colon.

07:45

The

07:47

sorry,

07:48

The

07:54

Bravo. You see, this is a little bit knowing to type

08:00

and then re and then semi Colon. Then see Hama

08:11

jumpy.

08:13

And let's put another road there. So let's say the

08:22

dealt ah

08:26

closed this

08:26

and let's go

08:28

no further. So let's do it just for 1st 4 letters. Otherwise we would have to type everything there.

08:35

Alternative would be to create a constant out of this

08:41

array, which is a one through

08:46

B 26 then put it there, or just named this constant or whatever. But it's just for the

08:54

example. Just go with, come to we close it

09:01

and yeah, sorry we have for gotten to put the

09:07

I guess you see how complicated is is? This might be.

09:11

And then we close this and we just press enter and get Alfa. So if you put see here, you'll get Charlie. If we have typed more than that, we would get, for example, if you Why here?

09:24

You still get Delta. Because we have only four

09:28

off these. Uh, this is the thing. So we had to be typed properly. But this is the way to use a raise in complex look up comments like we look up and to get the very nice results.

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

Similar Content