8 hours 33 minutes
So let's continue
with age. Look up and we look up, commends or functions
I'm going to explain everything for real. Look up because it's much more often used on dhe. Everything works for H. Look up, just it's ah
transposed. So we look up is looking Collins age hookup is looking at Droz,
so we look up functions, looks at the value in the left, most cone of table or selection or range,
and then returns of value from same row. But from defined column, meaning you defined and calm in the strange.
And if what you're looking up is matching with the entry in the
left most cone,
it will return to data from some other corn in the strange
Some threat too complicated and actually be able Cup is one of the commands that give hard time to pretty much anybody who starts first using it.
So imagine we have some sales data here,
we want to select the discount,
and based on this council, it's a
you can select whatever is year,
and we want to get how many units were ordered to in order to get this discount. Is there a correlation between the amount of discount and the number of units ordered. We just want to take a look it,
so we're not
going to use that Some for some further calculation. This this command is good for analyzing the data,
so we're going to say,
so the look of failure is what we're looking for. It's this
and then table array
And then we have the column index number.
This is called E, but it's not became a type B here.
We have to type 12345 So it's the fifth column in the range,
and we have to type here false.
And I explained that
in a minute.
So if you choose seven, we get the units or that
So wyffels, because we look up, expect
the data in first comb of the range are sorted
in some way, either ascending or descending.
And if we don't have that, we have to put false so that it's going to be exact match. Otherwise, this is going to be a problem for Ville cup commend.
Also, what is very important
we have two different to same entries in the different positions in different rose within the range
we look up is going to find first and return the value.
So we have more than one. We're going to disregard
any of these other Williams except the 1st 1 in the range as it is displayed
of the screen.
So this is the limit off the local function have multiple entries of the same value, which we're looking for. We're not going to get them because we look up, Ken,
return just one.
And there is no option to say Toe, look up. Okay. Giving the 1st 1 or the 2nd 1 or the 3rd 1 or
It just works with the first. So this is the limitation off. Look up functions I was talking about,
uh, we look up function is mighty powerful, but it has limitations.
The 70 things is for H. Look up just then. Ah,
the table would be entered so that we have the
the designations off data in the first cone and they're entered in Droz. Most of the people don't do that.
That if they do angel copies for them,
So the next one is hyperlink,
and it's Ah, it's where a simple function.
It creates a hyperlink
toe, a certain
in in in the field, in and sell in
So what's this outside can be. It can be, Ah, some file
or, for example, some video that you want to play and it's on your hard drive and then you just create the full path and you get what you were looking for. So, for example, here,
if I click here,
I will get
the link to hyperlink function on Microsoft site. So this is the link that you have in blue in the in the box, describing the hyperlink function when you go toe
and you get this link. So this is the
identical link like the one we had before.
So this is it.
It can also be It could be good point to a file or something else.
so the next function is index,
and it returns the value of reference of the cell in the intersection of Runcorn given range.
How to explain it
other than one example.
and we hear type index,
we open it, and then we define the ray
and then we say Roll number, Let's say two
and let's see Cole number mystery
it's 56. So road to come three.
as some other look up functions, when you look at it like this, it makes absolutely no sense to use it. You can just 0.2
which we get
with these numbers. But if these numbers are variable and come from some other calculation, or it's used in a loop in
in, ah, visual basic program or macro
and it makes absolute sense because then, for example, in the loop you can stroll to through every row and every column and do something with these numbers.
So this makes absolute sense
and the next function is indirect,
and it returns the value, our reference of the cell in the intersection off road and corn given range.
slightly different than,
uh, what you have seen
So we have here references of the cells.
So we see
We close it
and get 23 because a one is 23.
If you have clicked on the
we would get 67 because indeed, three we have C one, which is 67 now. This also can be, uh,
designation of, ah, cell within the range that has bean calculated in some way getting some data using some fine functions in something like that
and that it would make sense. This also makes no sense, but it would make absolute sense if we would create this C one from some inputs that we get from some fine functions or look at functions, we will cut function, whatever.
this concludes this part off
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