8 hours 33 minutes
in previous module,
you have been able to learn a lot about how to use a phrase functions.
And in this module we're going to pay much more attention to the functions themselves.
So we're going to look
at Matt functions, text functions, information functions, look up functions.
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.
So, um, as for math functions, which is the first lesson off this module,
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.
I'm not going to talk about trigonometry and logarithmic functions.
They usually have just won the argument. They have a little practical value to general user,
the people who need to use them usually know how to use them anyway,
as if you needs oregano metrical. Great nick functions. You're probably doing some kind of math calculations,
and there's probably nothing special
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
is going to be abs, aggregate Arabic ceiling, decimal, even floor and
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.
And I'm going to start E immediately with explaining how the cell functions work with her. What is their simplex?
So when you go to the formulas Tab, Excel, Quicken insert functions,
you get it this detailed description off its syntax.
So you picked the function you need
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.
And for each of these values there is a short description what should be in it sometimes thes descriptions air
not so clear
and not so easy to understand.
But in that case, you have at the bottom left corner off this small window,
Um, a link to help on this function, which will take you to the Internet page, explaining in detail how to use the function,
and they're going to also be examples of usage of dysfunction.
And now we should start directly with some thes functions.
And the 1st 1 is ABS.
It has just one argument, and it turns of absolute value of a number, so it's rather simple.
You just, uh,
go to excel.
We have some list off these
numbers that we want absolutely off. So let's do it the way I have explained.
And you just type here, abs, you pressed, enter, you get dysfunction, you double click on it, and you get this full description with
the argument you have to put in. And in this case, we're going to put this cell
and we're going to say OK,
and then we're going to expand it to the whole range, and you see
at the return of the absolute function is always positive number. It can be also
the serial number,
and it will remain the decimal number.
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.
It is useful when you're using it with other functions that absolutely require
ah, positive number in order to calculates and rhythmic functions. Some people square root
if you If you put the negative argument in that function, you will get nearer
also, if, uh,
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
similar function to this one
is a sign
just give you a sign of the number. So if number is negative,
it will give you
If it is zero, it will give you zero. If it is positive,
it will give you
So if we expand this
you see this this will be the
Always, always positive is one. If we have the error in the cell, it will replicate that terror. So this is how this function
So this is the syntax of sign. It's nothing special, has just argument, like absolute.
But now we go to a very complex function
which is called aggregate,
and it has multiple argument lists. So
let's go to the 1st 1 The first argument dysfunction number.
So basically what that aggregate does,
uh, is it picks
the number you have placed his first document. According to that number, it's chooses
which function to use.
And it is one of these 19 thatyou have you that you see now on the screen
so it picks a function according to your number. For example, if you select No. Nine, it will be some.
There are some statistical functions there, some mad functions. So
thesis is, uh, this is, uh,
at the first moment, quite confusing. Why would we use one function to use
the second function when we could have used that function in the first place? And the reason is
the second argument, which is an option.
if the option was zero or none, meaning that the second argument is,
used looks entered,
eating ignores nested sub total and aggregate functions.
If it's one, it ignores hidden droz nested Sepp Toto on aggregate functions and so on. And so
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
So let's go to the example
off aggregate function. We have here a list of numbers, including one with the message, and we're going to say OK,
egged great, sorry,
And let's say we want to some. So the function number is nine
and then we want to ignore Hidden Rose and there were aliens, which is seven.
And then we have an array
and we can close
and report, press enter.
And here's what we get minus 273.
So the Row nine,
the error message was ignored.
And now if we hide these two rows,
it's going to change because now it didn't take
in consideration the hidden droz.
So aggregate is extremely powerful function, but it's obviously quite complex to use, so you have to be really, really careful
on using them
on typing the syntax.
Also, if you want to go into further detail off aggregate function, I suggest that you use help in excel
and it will give you a very very detail page with quite a lot of examples.
Also, to show you the difference between aggregating one of the function it is using.
Let's go with this sum.
Let's say some off
and then go
So it's going to give you an error because just one of the cells has an error. Let's, uh,
number of rose by one and go to grow
It will give you,
Cuban Rose. Now, if we hear also goto a eight,
we'll get the same thing. But now when we
this will change. This one won't. So
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.
There's some kind of help when you analyzed it
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