Video Transcription

00:01

in previous module,

00:03

you have been able to learn a lot about how to use a phrase functions.

00:09

And in this module we're going to pay much more attention to the functions themselves.

00:15

So we're going to look

00:18

at Matt functions, text functions, information functions, look up functions.

00:25

And we're going to process each of the Children chosen functions in detail, showing examples and show you the syntax of these functions and how to use them.

00:39

So, um, as for math functions, which is the first lesson off this module,

00:45

I just want to mention that there are more than 70 math and trigonometry functions in X. Lt doesn't 19 and we're not going to talk about all of them.

00:55

I'm not going to talk about trigonometry and logarithmic functions.

01:00

They usually have just won the argument. They have a little practical value to general user,

01:07

and, uh,

01:08

the people who need to use them usually know how to use them anyway,

01:12

as if you needs oregano metrical. Great nick functions. You're probably doing some kind of math calculations,

01:19

and there's probably nothing special

01:22

that I can explain to you that you don't already know if you need to use dysfunctions. So the malfunctions were going to cover this module, or this lesson

01:32

is going to be abs, aggregate Arabic ceiling, decimal, even floor and

01:38

mud. Odd high product quotient. Rent. Run between Roman round, round up, round down sine squared or risk your piece of total sum if survives and trunk.

01:53

And I'm going to start E immediately with explaining how the cell functions work with her. What is their simplex?

02:04

So when you go to the formulas Tab, Excel, Quicken insert functions,

02:12

you get it this detailed description off its syntax.

02:16

So you picked the function you need

02:20

and you get something like this. This is for, for example, for the local function. I have chosen this one because it's very complex. It has four arguments, at least for I mean has to have for Argument's. A three minimum and forth is optional one.

02:36

And for each of these values there is a short description what should be in it sometimes thes descriptions air

02:44

not so clear

02:46

and not so easy to understand.

02:50

But in that case, you have at the bottom left corner off this small window,

02:57

Um, a link to help on this function, which will take you to the Internet page, explaining in detail how to use the function,

03:05

and they're going to also be examples of usage of dysfunction.

03:10

And now we should start directly with some thes functions.

03:15

And the 1st 1 is ABS.

03:20

It has just one argument, and it turns of absolute value of a number, so it's rather simple.

03:27

You just, uh,

03:29

go to excel.

03:30

We have some list off these

03:35

numbers that we want absolutely off. So let's do it the way I have explained.

03:40

And you just type here, abs, you pressed, enter, you get dysfunction, you double click on it, and you get this full description with

03:53

the argument you have to put in. And in this case, we're going to put this cell

04:00

and we're going to say OK,

04:01

and then we're going to expand it to the whole range, and you see

04:05

at the return of the absolute function is always positive number. It can be also

04:13

the serial number,

04:15

and it will remain the decimal number.

04:18

It is just that if it's negative, it changes its sign. If it's positive or zero, it doesn't do anything. You get this, and this is the proper mathematical function of absolute number.

04:31

It is useful when you're using it with other functions that absolutely require

04:38

ah, positive number in order to calculates and rhythmic functions. Some people square root

04:46

if you If you put the negative argument in that function, you will get nearer

04:51

result.

04:55

Um,

04:57

also, if, uh,

04:59

people are entering some daytime, you expected to be positive. You can always in further calculation, use first The absolute value off what they have entered

05:10

similar function to this one

05:15

is a sign

05:17

and sign

05:19

will

05:20

just give you a sign of the number. So if number is negative,

05:28

it will give you

05:30

minus one.

05:33

If it is zero, it will give you zero. If it is positive,

05:39

it will give you

05:40

one.

05:42

So if we expand this

05:44

you see this this will be the

05:46

same.

05:49

Always, always positive is one. If we have the error in the cell, it will replicate that terror. So this is how this function

05:59

works.

06:01

So this is the syntax of sign. It's nothing special, has just argument, like absolute.

06:09

But now we go to a very complex function

06:12

which is called aggregate,

06:14

and it has multiple argument lists. So

06:19

what's Ah,

06:21

let's go to the 1st 1 The first argument dysfunction number.

06:26

So basically what that aggregate does,

06:30

uh, is it picks

06:32

the number you have placed his first document. According to that number, it's chooses

06:39

which function to use.

06:42

And it is one of these 19 thatyou have you that you see now on the screen

06:47

so it picks a function according to your number. For example, if you select No. Nine, it will be some.

06:56

There are some statistical functions there, some mad functions. So

07:01

ah,

07:02

thesis is, uh, this is, uh,

07:08

at the first moment, quite confusing. Why would we use one function to use

07:12

the second function when we could have used that function in the first place? And the reason is

07:17

the second argument, which is an option.

07:20

So

07:23

if the option was zero or none, meaning that the second argument is,

07:30

um

07:32

not

07:33

used looks entered,

07:36

eating ignores nested sub total and aggregate functions.

07:43

If it's one, it ignores hidden droz nested Sepp Toto on aggregate functions and so on. And so

07:49

one of the most used is seven worried when it ignores hidden rose and air will use or six win, it ignores their values or maybe sometimes in five. And it ignores Hidden Rose. If you're sure that you won't have a rare values in your

08:07

ah table.

08:13

So let's go to the example

08:15

off aggregate function. We have here a list of numbers, including one with the message, and we're going to say OK,

08:26

egged great, sorry,

08:30

Aggregate.

08:31

Okay.

08:33

And let's say we want to some. So the function number is nine

08:39

and then we want to ignore Hidden Rose and there were aliens, which is seven.

08:46

And then we have an array

08:48

or range

08:50

and we can close

08:54

Records

08:56

and report, press enter.

09:00

And here's what we get minus 273.

09:03

So the Row nine,

09:07

the error message was ignored.

09:09

And now if we hide these two rows,

09:13

it's going to change because now it didn't take

09:18

in consideration the hidden droz.

09:20

So aggregate is extremely powerful function, but it's obviously quite complex to use, so you have to be really, really careful

09:31

on using them

09:33

on typing the syntax.

09:37

Also, if you want to go into further detail off aggregate function, I suggest that you use help in excel

09:50

and it will give you a very very detail page with quite a lot of examples.

09:56

Also, to show you the difference between aggregating one of the function it is using.

10:01

Let's go with this sum.

10:03

Let's say some off

10:07

and then go

10:09

here.

10:11

So it's going to give you an error because just one of the cells has an error. Let's, uh,

10:18

we

10:18

twos

10:20

number of rose by one and go to grow

10:24

eight.

10:26

It will give you,

10:28

uh,

10:30

some, including

10:31

those

10:35

Cuban Rose. Now, if we hear also goto a eight,

10:39

we'll get the same thing. But now when we

10:43

hide,

10:45

this will change. This one won't. So

10:48

if you want to do some calculation, you have a list of people, their performances. You want to look at the total, and you want to say, Okay, let's see how it looks without one of these are a few of these. Just rebuild these rows and you automatically see the number, so it's very good for using Excel.

11:07

There's some kind of help when you analyzed it

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