Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

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

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor