Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
so the final lesson this model is about, look up functions
00:07
and look up functions are those functions that makes Excel extreme for two.
00:13
So you have seen how we can work. They raise how we can do some math calculations, how we can manipulate text.
00:22
But Indy and Excel is about getting information from Road eight entered table off course. There are some other usages of excel bit in most applications. We get a lot off lots of numbers or textual datum, and we want to extract something. We want to analyze that data. So
00:42
in most of the cases with old look of functions, you can find what you need
00:48
and combined with math and text functions and Aires. They can help you get almost any calculation from daytime saying almost any
00:57
because there is a limit to capabilities of look functions.
01:03
And, uh, if you want to go further and they were really complex analysis of data, our do something really complex, useful with it.
01:12
Ah, you will need some micro's and some programming because
01:18
they're simply some things that haven't bean
01:21
programmed in tow, Excel look of functions.
01:23
So we're starting with the function called dress and it creates seller references. A text. It's a relatively simple function.
01:34
So, um,
01:37
you put, uh,
01:41
the number of throw and then the number of colon.
01:45
And then you put this absolute number, which is actually way how you define what they're going to in what form you're You're going to get the address back. So if you put four, you get all
02:01
ah, you get the address, See, for meaning for throw
02:07
there had calm. So it's C four
02:10
in a relative for mitt.
02:13
And if you put the one, you will get it in absolute for mint.
02:17
And if you put too,
02:22
just the row is going to be absolute and put three.
02:28
Then the column is going to be an absolute form, earth and roads going to be relative. So this is
02:34
what dysfunction does. So you get the address off the cell now in this form, and it actually doesn't make any sense, because if you know the constant off the roll and calling, you can just
02:49
do this
02:50
typing by your own. But if these air variable numbers that come out off some kind of calculations, some kind of fine functions, some kind of look up functions,
03:00
then you can do a little bit more. Also, if you include that in some kind of macro or visual basic for application program, then you can do really powerful things.
03:14
Next function I'm going to talk about is choose and basically chooses the value erection to perform. And to understand that with me, put it this way. So
03:28
it chooses third number from this list, and this list is a one. A two, a three. And I'm choosing the number three
03:37
now the again, the way I typed it. It makes no sense because I if I'm seeing a one a two, a three and I'm choosing the third number from West. I know it's a three. Uh, the power of this function comes when these air variable,
03:53
uh, perimeter's, which I can then later pick up from, for example, here, at least one of the variables this one is one of the parents is variable. So basically, I want to
04:08
create the product off this cell and
04:15
the 1st 2nd and third cell from this range.
04:18
So, um, I have this e one and then we have cell C one sea to see tree, so If I put here one,
04:28
I will have
04:29
a two, which is 11 multiplied by 33 which is 363. And if I changed this to three, I will get 726 which is 11 multiplied by 66.
04:42
Very simple. Now, if all of these variables can be chosen from some calculation, then you will actually get what you really want
04:53
again. This can be really, really great if you're using this even macro,
05:00
So formula text is the next function
05:02
and it's simply retails formulas text it also
05:08
pretty much makes no sense by itself. So we have four cells. We have, ah, in fifth sell some of these four cells and here we say formula text off a five. What we get is
05:20
text that represents this formula.
05:24
Now then, we can use this to manipulate this text and return. It is the formulas somewhere else. And this is the power and
05:33
importance of dysfunction.
05:38
And the final one we're going to talk about in this part off.
05:43
This lesson is get people data
05:46
and get people data simply extracts data from the table. Now, usage of this
05:53
function is rather complicated
05:56
typing it so that you do it properly can be real
06:00
problem. And if you look here, we have some road data. And then we created the pivot table from the true data.
06:08
And, uh,
06:11
we created here
06:13
some cells that point they record here
06:16
to this one. This one and this one.
06:20
Uh, we didn't do it by typing. So if we say
06:27
get people data me, open it, then we need the data field, which we're going to point to the people table, et cetera, et cetera. It can be really complicated thing, too.
06:41
Type properly.
06:43
So what? We can do it. Instead of that, we can just click on the cell and we get
06:48
all the things we need about
06:51
get people date. The function here is Well, just press enter in that cell
06:57
and excel does it for you.
07:00
And this is how I suggest to use Get people. They function if you need it.
07:04
So this is it.
07:08
And if we change the filter
07:12
and includes another
07:14
customer?
07:18
Yeah. Okay. So these cells the
07:24
they're in a way off this. You updated people tables were going to delete them. But we have these here, and
07:32
this is very
07:34
precise
07:35
direct information of these three cells. If we now includes third
07:43
customer, get it even
07:46
for all of them. And we get to the full number here.
07:49
And if you look at this some here, it's equal to the grand total we have here. So if we need some data from the tape people table, you just select these cells
08:01
and you can put them in some other calculation, probably in some other worksheet.

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