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

Similar Content