Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
as we continue this lesson that is
00:04
about Matt functions. We come to the function called Bodh. It's relatively simple function,
00:11
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
00:26
and what Israel left in terms of integer is the more returns.
00:33
It's very simple.
00:35
No
00:38
issues about using it. It's clear what it does
00:44
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
00:59
we get the remainder of three. And also, if we divide minus certify with six, we actually get them. Reminder one,
01:07
because minus 35 is it's a negative number. So it works the other way around now, too, actually,
01:15
du monde function. You can do it without using it. You can make a formula which goes like this
01:49
and then
01:53
stretching to the whole range. We get absolutely same real. So basically, mod is a function that saves you from typing all of this.
02:02
So next function, you're going to talk about this product
02:07
and product returns.
02:09
Ah,
02:10
a product of all arguments within the function can be used with areas. But what is important that the number off,
02:21
um, arguments is limited to 255?
02:24
Um,
02:27
so, um,
02:30
to make an example of it, we have some numbers just type product
02:37
on, then this
02:40
this and for example, comer. This
02:44
it doesn't matter. They don't have to be in one the rover one.
02:47
Come on.
02:50
And you get 88 just two times four is eight times 11 is 88. But also we can use this
02:58
with range or Ray,
03:00
so you can just select all of these numbers
03:06
percenter and you get 180 which is four times five is 20 times Stein. Its 180
03:15
You can also have multiple range is there
03:19
so you can see product
03:22
and didn't see
03:23
this range
03:25
no
03:27
supplied
03:28
with.
03:30
For example,
03:31
this one doesn't have to be a range,
03:36
and it's 900 because it's a 100 multiplied by five is 900 or chicken just type
03:46
and
03:47
this'll comer
03:50
this
03:52
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
04:05
same number. No differences.
04:10
So the next function
04:14
is quotient.
04:16
An Eter returns the interview portion of division.
04:19
No,
04:21
but it means is that
04:26
if we have motion toe
04:28
a one and be one,
04:30
it gives us the interview portion, which is one because if we divide these two numbers,
04:36
we get
04:42
this.
04:43
And actually this function is the same, like
04:48
this one divided by this one.
04:53
It also works very nicely.
04:57
Us
04:58
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
05:09
it's a negative number, so
05:11
the next function
05:14
is
05:17
hi.
05:19
So, for example, if you want to calculate the surface of the circle,
05:25
suffers of the circle is
05:28
equal to radius
05:30
squared,
05:32
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
05:44
and presenter because Radius zero happens. Nothing zero but put too.
05:53
We get it like this and the precision of pies after 15 digits. So case you 15 digits
06:00
decimal sorry off number by
06:06
So
06:08
next functions
06:11
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.
06:25
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
06:45
statistical, uh, calculations to the actual random numbers. But they are not 100% random.
06:55
So, uh,
06:56
where's the number
06:58
is simply
07:00
we type around numbers that we see
07:04
brand
07:09
Sorry.
07:13
And we get this.
07:15
And, uh, this number is always greater or zero, but less than one.
07:24
And also, if we do something again, so type begin random number,
07:30
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.
07:48
Now the function ran between
07:53
is pretty much the same on Lee. You define bottom in top numbers,
07:58
so
07:59
if we say
08:03
grand
08:05
between and we say, for example, one and the seven,
08:16
we'll get this number. You see, these have also been recalculated, and we stretch this a lot.
08:24
You get the numbers that can be that they're always
08:28
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
08:43
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,
08:58
so
09:01
next function is simple. It's square root. Ask your tea,
09:05
and
09:07
if we put it s Q R D
09:11
manly put. Remember here
09:15
and we get the square root
09:18
and you'll notice that
09:20
number
09:22
here is not possible because we're trying to get square root of negative number, which is kind of a possible with real numbers.
09:31
So if you want
09:33
this to work any time, you just put
09:39
M suit
09:41
inside the square root function
09:43
and you get it like this. So if you put here with minus 25 you'll get five, which is expected.
09:56
Okay, Now we have another complex function, which is called sub total.
10:03
And if you remember aggregate function,
10:05
this is working in a pretty similar way.
10:09
So
10:11
it is meant to work with the list or database,
10:16
and the first number is function number,
10:20
and it can be 1 to 11 or 101 to 111.
10:26
Um,
10:28
if it's between one and 11 then it includes here themselves.
10:33
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.
10:52
And, uh,
10:54
let's, uh,
10:56
go to sub total tab
10:58
and let's go to it.
11:01
So
11:03
you say
11:03
nine comma,
11:07
then this'll range here
11:09
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
11:20
bar here you get the same number?
11:22
No.
11:24
If we hide this,
11:26
nothing changes.
11:30
But if we now put here 109
11:35
we get a different number. And if we unhygienic is cells,
11:41
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
11:56
it really got your text.
11:58
Uh, but it won't ignore,
12:01
uh,
12:07
like division by zero. So if you have sell the number that the error, you'll get an error here as well.
12:18
So this is sub total.
12:22
And, uh,
12:24
then we have two more functions. Some, if in some ifs
12:28
which are very helpful in getting the data dating. Some sums out off various data
12:39
in the data she did have bean entered.
12:41
Ah, Brenda M'lee.
12:46
So if you haven't structured data
12:48
ah, you you can look up on and see within the range. So in the range, you look on the cells you want evaluated.
13:00
Then you have created a rhea,
13:01
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.
13:11
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.
13:22
Then you would do something like this.
13:26
So you would say
13:28
that is equal
13:31
If
13:33
and then we look at the range and the ranges, for example, the name off people
13:39
and then criteria would be it's
13:41
equal
13:43
Tom
13:45
and then the range in the vitriol looking for It's here where we have the numbers,
13:50
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
14:05
every cell within range is equal to the condition, and then
14:11
it
14:13
takes
14:16
the number from the second range
14:18
and adds it to the sum if the condition is met
14:22
now, um,
14:24
it can also look at the same range, so you can also see
14:28
so some if
14:31
and then we look att, this range
14:33
and then we look at the condition and say it's smaller than 50
14:39
and, uh,
14:43
sorry, let's say 55
14:46
and
14:48
then just close it
14:52
and we get 123.
14:54
So we
14:58
get
14:58
some off all numbers here.
15:03
She's not home three Richard smaller than 55. So it's a very convenient function that can be used in many
15:13
situations.
15:15
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.
15:24
So, um, we can have criteria, rage, run and pretty rearrange, too. And more
15:33
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
15:43
some ifs broken.
15:46
And then this is the range with in which we are looking for numbers which are goingto ad.
15:52
And then we have the first criteria range,
15:56
and then we have
15:58
first criteria,
16:03
and then we have second criteria range,
16:07
and then we have a second criteria,
16:11
and then we close this and 46.
16:17
And if you look a year,
16:19
we have two chairs the Tom sold and Total Sons for six. So this is how this do functions work

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