Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

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

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor