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

Similar Content