8 hours 33 minutes
as we continue this lesson that is
about Matt functions. We come to the function called Bodh. It's relatively simple function,
and this function is ah, returning remainder after product after a number is divided by devices. So basically, if you have two numbers, you make you divide first. But the 2nd 1
and what Israel left in terms of integer is the more returns.
It's very simple.
issues about using it. It's clear what it does
extend this to the whole range of examples. You see that if we divide 21 with seven, the remainder is zero because 21 divide the same ministry and nothing left. But if you do that 24
we get the remainder of three. And also, if we divide minus certify with six, we actually get them. Reminder one,
because minus 35 is it's a negative number. So it works the other way around now, too, actually,
du monde function. You can do it without using it. You can make a formula which goes like this
stretching to the whole range. We get absolutely same real. So basically, mod is a function that saves you from typing all of this.
So next function, you're going to talk about this product
and product returns.
a product of all arguments within the function can be used with areas. But what is important that the number off,
um, arguments is limited to 255?
to make an example of it, we have some numbers just type product
on, then this
this and for example, comer. This
it doesn't matter. They don't have to be in one the rover one.
And you get 88 just two times four is eight times 11 is 88. But also we can use this
with range or Ray,
so you can just select all of these numbers
percenter and you get 180 which is four times five is 20 times Stein. Its 180
You can also have multiple range is there
so you can see product
and didn't see
this one doesn't have to be a range,
and it's 900 because it's a 100 multiplied by five is 900 or chicken just type
and it will give us the product of these numbers multiplied by private business is basically a full range. This also gives the same result. Who here but something like this
same number. No differences.
So the next function
An Eter returns the interview portion of division.
but it means is that
if we have motion toe
a one and be one,
it gives us the interview portion, which is one because if we divide these two numbers,
And actually this function is the same, like
this one divided by this one.
It also works very nicely.
and the horse. Remember, you get is minus six, and you have to worry about instincts if you don't understand just tested. But because it's a
it's a negative number, so
the next function
So, for example, if you want to calculate the surface of the circle,
suffers of the circle is
equal to radius
multiplied by by. You have to punt empty apprentices at the end off by because then Excel knows it's a function at the name
and presenter because Radius zero happens. Nothing zero but put too.
We get it like this and the precision of pies after 15 digits. So case you 15 digits
decimal sorry off number by
our brand, which is absurd. A random number between zero and on. And why do we say suitor and the number? Because Excel is using ah algorithm to calculate this number.
So no matter how good the algorithm is, after some time, you get the repetition and the sequence of these random numbers starts again. Now the sequence is a very large so you can treat this is when the number they have bean These algorithms have bean designed so that they give very, very close
statistical, uh, calculations to the actual random numbers. But they are not 100% random.
where's the number
we type around numbers that we see
And we get this.
And, uh, this number is always greater or zero, but less than one.
And also, if we do something again, so type begin random number,
you will notice that the 1st 1 has changed because every time you re calculate the Excel sheet, Excel also Recalculates all the random functions again. So you get another number in another number number in the same self.
Now the function ran between
is pretty much the same on Lee. You define bottom in top numbers,
if we say
between and we say, for example, one and the seven,
we'll get this number. You see, these have also been recalculated, and we stretch this a lot.
You get the numbers that can be that they're always
bigger or greater or equal the first number and, ah, smaller with second number. So you see, here we have seven. So if you're doing some kind of
calculation, which requires random numbers or you want to create an actual sheet that will simulate, I don't know lottery or something, it's very useful. Otherwise, there is not much use of functions,
next function is simple. It's square root. Ask your tea,
if we put it s Q R D
manly put. Remember here
and we get the square root
and you'll notice that
here is not possible because we're trying to get square root of negative number, which is kind of a possible with real numbers.
So if you want
this to work any time, you just put
inside the square root function
and you get it like this. So if you put here with minus 25 you'll get five, which is expected.
Okay, Now we have another complex function, which is called sub total.
And if you remember aggregate function,
this is working in a pretty similar way.
it is meant to work with the list or database,
and the first number is function number,
and it can be 1 to 11 or 101 to 111.
if it's between one and 11 then it includes here themselves.
And if it's between 101 and 111 it does not include himself and the functions you see Average count, count a backspin products and the deviations, some and so on and so on. So let's work with number nine to some.
go to sub total tab
and let's go to it.
then this'll range here
and it close it and we get 324. And if you do this and look ATT, the sum that his excellent of calculated automatically here in the
bar here you get the same number?
If we hide this,
But if we now put here 109
we get a different number. And if we unhygienic is cells,
we get the full sum off all seven gross. What subtitle doesn't have is an option to ignore error messages or that's put years Text
it really got your text.
Uh, but it won't ignore,
like division by zero. So if you have sell the number that the error, you'll get an error here as well.
So this is sub total.
then we have two more functions. Some, if in some ifs
which are very helpful in getting the data dating. Some sums out off various data
in the data she did have bean entered.
Ah, Brenda M'lee.
So if you haven't structured data
ah, you you can look up on and see within the range. So in the range, you look on the cells you want evaluated.
Then you have created a rhea,
and then you have optional some range. So if you want to some numbers from a different range than the one in which you are looking at.
For example, if you have some names and you want to see people who made some sales and you want to see how much they made in total sales.
Then you would do something like this.
So you would say
that is equal
and then we look at the range and the ranges, for example, the name off people
and then criteria would be it's
and then the range in the vitriol looking for It's here where we have the numbers,
we close it and we get 1 73 and this is the some off these or cells just jackets 173. So this is how some if works, it checks. If
every cell within range is equal to the condition, and then
the number from the second range
and adds it to the sum if the condition is met
it can also look at the same range, so you can also see
so some if
and then we look att, this range
and then we look at the condition and say it's smaller than 50
sorry, let's say 55
then just close it
and we get 123.
some off all numbers here.
She's not home three Richard smaller than 55. So it's a very convenient function that can be used in many
Now, if we look at some ifs, it works pretty much the same thing. But now the number off conditions can be more than just one.
So, um, we can have criteria, rage, run and pretty rearrange, too. And more
so to do this year. We'd, for example, want to see how what is the total value off all the chairs that time. So So So we say
some ifs broken.
And then this is the range with in which we are looking for numbers which are goingto ad.
And then we have the first criteria range,
and then we have
and then we have second criteria range,
and then we have a second criteria,
and then we close this and 46.
And if you look a year,
we have two chairs the Tom sold and Total Sons for six. So this is how this do functions work
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