8 hours 33 minutes
Okay, Let us now continue with more complex formulas using a raise.
always go their record to the example.
And here we have, uh,
list off products that one companies selling
lying Lawson waas
out of many.
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,
they get information to the head office and they put it in excel sheet.
And what we want to determine is what was the best
sales price achieved for each item.
And because thes date, this data has entered completely without any order,
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.
So what we're going to do is we're going to go by and Max and if
and a raise,
and we're going to say, okay, we're looking for a maximum off,
and then we have a condition if
to wine glass,
we go here
and pick the
And if it's not, we're just going to help. False
or we can put zero
and then we're going to close. If and then we're going to close. Max
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.
We can do the same thing for us. So we're going to go,
And then if
and then again,
product name should be equal
He too, which is Woz.
And then, in case that is true, we're going to look at the field, which is
cold. Aye, or selling. Call me
otherwise it's going to be false or zero
closing GIF closing Max
Patrol shift enter
and we're getting 39.5.
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
ways would be to have here command function if
and then again, we say, if this
do the wine glass.
equal to the wine glass.
Otherwise it is zero
it close it
and then we expand this through entire range.
And we, uh
we made a mistake.
let's do it again.
So the alternative would be
without using Get Reyes is to have intermediary cones. And this one would be, for example, if
this item is equal
to this item
and we're going to make it with strings
so that it doesn't change when we
open the cell, then is going to be this otherwise is going to be zero.
So we're going to close it
and then we get this, we expand this
and then we can make the max out off
this range here
and we get again the 29 which would be the correct calculation. So you see how complicated this is Also, if we would
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
because then we would use the
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,
so this would complicate things.
And the this shows you how complex
the functions or formulas you can use a raise to great benefits.
Now, let's move to next example.
Which would be this. So we have this Ah, list.
And, uh, this is, uh,
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.
just the type here, a letter here
in cell C one and you want in d one to get the equivalent spelling off the letter.
So the classical or traditional way to do this would be two years. We look up Calmund or function,
and we would, uh,
look for this
this range here.
And three answer would be from in column B, which is second column in the selection,
and we can just close this
and we will get off. So if we type here B,
you get Robbo and so on and so on.
The problem with this is that we need to have the stable somewhere,
so there is another way to do it. So let's say that our reference letter will be
you in. So Hee won
and we want to do just with having one function. We're also going to go with Libby. Look up.
But we're going to look at this inn.
That array constant, Which looks like this.
Bravo. You see, this is a little bit knowing to type
and then re and then semi Colon. Then see Hama
And let's put another road there. So let's say the
and let's go
no further. So let's do it just for 1st 4 letters. Otherwise we would have to type everything there.
Alternative would be to create a constant out of this
array, which is a one through
B 26 then put it there, or just named this constant or whatever. But it's just for the
example. Just go with, come to we close it
and yeah, sorry we have for gotten to put the
I guess you see how complicated is is? This might be.
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?
You still get Delta. Because we have only four
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.
Data Analyst Excel
The Data Analyst Excel Test is a premium Cybrary assessment created by iMocha and intended ...
Monitor a Web App
In this IT Pro challenge, participants get hands-on experience activating server logging and using Application ...
Learn On Demand