# 3.1 Math Functions Part 2

Video Activity

Join over 3 million cybersecurity professionals advancing their career

Sign up with

or

Already have an account? Sign In »

Video Transcription

00:01

let's continue this lesson.

00:03

We do function called Arabic,

00:06

and it has just won the argument, which has to be in text form,

00:11

text format and Arabic returns, Arabic numerals from Roman numeral. So, uh, it's, Ah,

00:18

very simple way to do it.

00:21

And the other correlated function

00:23

is Roman, and he turns Roman numeral from Arabic.

00:28

But because there are five different forms off Roman rose, there is an option for form, which can be

00:35

from 0 to 4.

00:37

And zero is a classic Roman form of doing that

00:42

where and it goes to different stages till it reaches option for which represents very, very simplified version of writing Roman numerals.

00:52

Now,

00:54

if you if you're into the writing from the numbers, you will probably know what we're talking about. If not, just need this as a reference. So let's see this on on a couple of examples.

01:08

So ah, we have here. The number is 999. Anyone hurt in that 1325. So this is Roman number adoption zero. So this is classic Roman way of writing numbers, which means that we have first the designation off

01:29

hundreds so it's C m meaning,

01:32

ah, 100 subtracted from 1000 meaning 900. Then we go to tents and then we have ex subtracted from C, meaning it's 90 and then we have one subtracted from X, which means it's nine.

01:49

Uh, if you look it

01:51

slightly different way of fighting it l represents 50. So, uh ah,

01:59

it's a complex thing. I don't want to go into explanation because these things they can get very, very

02:06

complex, especially if we go with the

02:08

number two

02:10

or sorry,

02:14

Number three

02:15

as an option.

02:16

So it changes a lot.

02:20

What is important is the most simplified way, which means we just put I meaning one in front of a mean 1000. So we basically subtract one from 1000 will get night 1 999 Now,

02:36

with some other numbers which are not so close to two

02:40

hundreds of or thousands,

02:45

we get

02:46

completely same results regardless off options.

02:52

And on the other hand, if you want to use the Arabic function, we just used the Arabic and you see gives us this number. Now this is

03:01

comparison of how Arabic works with different forms of Roman numbers and it always gets the same result. So here we have

03:10

classic Roman here have some kind of simplified and then very, very simplified metal. But in every case ah, function Roman Britons, the correct Arabic numbers. So it's very important to know

03:25

Ah, the next function is decimal

03:29

and the syntax of this mill is very simple. We have a number and we have a form.

03:35

Sorry, we have ah

03:37

ah number and we have Ray Dicks or base in which the number is presented and this number can be text or number. But

03:46

in general, we should be in text format

03:50

because if you go with bases that they're higher than 10

03:54

then we get

03:57

need for letters in the representation of numbers. So it has to be text.

04:02

And, uh,

04:03

here is the explanation of it. So f in X additional means that we're having

04:12

number A, which represents 10 and it's in place on on tens. So it's 10

04:19

multiplied by 16. So it's 160 F represents 15. So it's 50 90 to 160 we get

04:30

175. Here we have a number with the base eight

04:34

and then we calculated. So it means one times whatever.

04:41

And let's not go into details is just there, presenters, If we would put here

04:47

number 16

04:49

This can also be extra decimal number.

04:53

And this is what we get as a result.

04:57

And here is the typical binary

05:00

representation. So, for example, binary number seven is 111

05:05

and we get seven.

05:08

Ah, So this is something that you can

05:13

use if you want to transfer numbers from different bases to already exes to the decimal number.

05:24

And now we move to a very complex

05:29

matter off rounding numbers

05:32

and ah, let's go first with the ceiling

05:36

and the function ceiling

05:40

does this,

05:42

it rounds the number to the first larger multiplier of significance.

05:46

Now it sounds complicated, but let me explain to you what it means.

05:50

Significance is what you put here,

05:55

and this is what you wanted to be rounded too. So if it's 0.5, it will be around it either toe integer or to something that goes,

06:06

Ah,

06:09

that ends with 0.5. So this

06:12

means that ceiling will around the number to the first higher number

06:16

that is

06:18

divisible without remains

06:23

with this number.

06:24

So, for example, if we have this number here,

06:29

which is 23.4567

06:33

and it's minus its ah,

06:35

it's this.

06:36

But if it would be positive number, it should be rounded to 23.5 because the 23.5 is first hire multiplication off 0.5

06:47

Here also weaken Do with

06:50

ah, uh, for example,

06:55

based one or significance. One anyth rounds to the first hire. Because why? Because 23 is higher or larger number than minus 23.6735

07:05

same goes with the positive numbers. So if we have

07:10

0.5 to go here,

07:13

is it all here But here, If we would have, for example, four

07:18

then is going to be 22.5.

07:23

So this is sealing

07:26

even

07:28

is a function

07:29

that runs the positive number two the first hire, even integer and negative number two the first lower even integer

07:38

So

07:39

how it works

07:41

Even

07:43

first lower negative number Ah,

07:46

uh, even number compared to number minus 23.6735 is

07:54

minus 24 because when we talk about negative was minus 24 is smaller than minus 23.6 by and when we talk about

08:05

positive numbers,

08:07

we go to the first.

08:07

Even

08:09

so,

08:11

the first number here higher integer number is 23 but it's not even it's all number. So we get 24 here is well, 24.

08:20

Let's now go to the floor function just to see the syntax. So it transit number two the first lower multiplier of significance. So it works pretty much like the ceiling. It just goes to the lower number instead of a higher number. So

08:37

floor

08:37

and then we take this number.

08:39

Close it.

08:43

Sorry.

08:45

See, you always have to.

08:46

They care that for example, we put here five

08:52

and we have to

08:54

too many

08:56

apprentices. But here it is.

08:58

So it goes down.

09:01

So here is 23.5. Here's 23. If we move this function around, we get

09:07

this

09:09

here.

09:11

So it's minus 24. Why? Because minus 23.67 is closest to the minus 24 so on and so on

09:20

It goes down.

09:22

Uh, minus 23.6735 is obviously closest to minus 23.5.

09:31

But because this is floor, it goes to the lower number every time.

09:37

Now Integer

09:39

is where simple one integer just returns the Britain's number to the nearest integer.

09:46

So if we have,

09:48

you're

09:50

integer off.

09:52

Sorry, this one

09:54

and we did. It's 23 if we talked with negative numbers.

10:01

Integer off minus 23.6 and 35 is minus 24. And if you look at this function, it works rather similar to floor.

10:13

So here, if you put here the number seven

10:16

instead of three,

10:18

this changes the changes. But

10:24

interviewer doesn't. Why? Because here we have 0.5. So if you put one here,

10:31

we got the same functionality in floor. We get the same functionality like with integer so

10:37

quick change this.

10:39

We get the same results.

10:43

Now Next function is round,

10:46

and it's the one that you probably be using the most, because it rounds the number to the first closest number that has Dessena lled defined with number of digits.

10:58

That means that the result off round doesn't have to be in danger.

11:03

And let's it's like this.

11:07

Have this and then we put here zero

11:13

and we get 23 so it works

11:16

in the usual rounding manner, so it rounds to the closest number.

11:22

Now, if we put here to decimals,

11:26

we get the decimal number

11:28

and then it gets. It performs the same rounding

11:33

ah,

11:35

logic like when it's an integer.

11:37

So if the number is

11:39

closer to

11:41

the higher so it's above.

11:43

If the last

11:46

digits are above

11:50

five, then it goes up. So it goes to four points point for six

11:56

and ifit's below. It goes

12:00

the other way around,

12:01

so it's classical rounding.

12:05

Roundup

12:07

does the same thing,

12:07

but it always goes to the high number.

12:11

So if you do

12:15

around the,

12:18

this number puts also two decimals,

12:22

which is something that is usually used in calculations. This is what we get. It always goes up. So if it's

12:28

4567 it's goes to 46. If it's for the miners 67 in those 2 68

12:37

if it's 78293 goes to 79

12:41

If it's ah

12:43

0.21247 it goes up to 1.3. Although the classical around and would go to 12

12:52

round down around down does the same thing. But it goes to the Lord.

12:58

So if we

13:00

and through this function c

13:05

down

13:07

olive

13:09

this number with two decimal points

13:13

and press it like this

13:16

and they attacked it,

13:22

bring it

13:22

These results.

13:24

So you see here

13:26

we got the classical rounding because it goes down and here it's not the classical rounding because it all again goes down.

13:35

Now trunk function

13:39

is slightly different. It has same functionality is integer

13:43

but you can defy number of decibels.

13:46

So

13:48

here

13:48

you say, drunk

13:52

out of this number and then we put

13:56

zero

13:58

and we get absolutely the same result is integer

14:03

But if we put one decimal,

14:07

it'll round to one decimal.

14:11

It actually is goingto

14:13

the same. Like we would, um,

14:16

multiplied the number by 10 do the integer and then divided by 10. This is the result

14:22

and in the end, the odd

14:26

function

14:26

that is absolutely similar way like even function.

14:31

But the result is on number.

14:35

So if you say odd

14:37

So it was just compare it first to the

14:39

even.

14:41

It has just one argument and see

14:45

odd

14:46

and this number

14:50

then move it.

14:52

So the result is always odd number and its rounded accordingly.

14:58

So this is how the rounding can be done in excel there quite a lot off these functions,

15:05

and you need to know which one to use when you do your surrounding.

15:11

So far you have seen, uh, how math functions can help you do quite

15:18

complex mathematical calculations.

15:22

Now

15:22

a CZ Muchas Excel is primarily to meant to be used for mathematical calculations. It can be also very powerful toe from manipulating text data.

15:33

The reasons for that might be very various. For example, you need to import

15:39

text data from other programs and they may not be formatted in the way you want them.

15:46

Uh, people entering text main making steaks with case, for example, they can enter

15:54

to capital letters in the name extent of one. They can, uh,

16:00

at extra spaces where they shouldn't be.

16:03

So all these things that when you type, for example, in inward, these things usually get off the corrected. Here in excel,

16:11

you have to

16:14

pay attention to these things. Also, if you exporting data to some other programs, you might be required to reformat your daytime too specific

16:26

type of for mating of texts in order for that

16:30

program to accept it. For example, if you have ah, no name and surname in in two separate cells

16:38

and the pure exporting to program that requires name and surname to be in one text drink, you need to connect them together and similar stuff.

16:49

So let's start with functions. First function is char,

16:53

and it basically returns. A single character corresponding to ask echoed off their argument number

17:00

and depression is why would you need that? Well, for example, you can need you may be in a position that you need toe add

17:11

like in this example here

17:15

Ah, line break within the text.

17:18

So when you see this text, it says sales 2019 Their space between sales in 2019 Here we don'ts have space. But we have,

17:29

uh, added character 10 s key code 10

17:33

and this character represents the line break.

17:37

Now, when you look a TTE this, you don't see much of it.

17:42

But, uh, if you form itself so that the text is wrapped,

17:48

you get this forced

17:51

enter or within the middle off the text.

17:56

Why would you need that? For example, in some tables, you would like in order to save space. Because there are so many cones.

18:04

You want the text to be broken in the middle in two lines instead of one line

18:11

so he doesn't overlap, or

18:12

there there should be a decent space between,

18:17

uh, explanations of calm. So in this case,

18:21

you would definitely need that.

18:26

Now the next

18:29

function we're going to mention is clean

18:33

and clean removes none printable characters from the text string.

18:38

And why would you need that? When you build your own

18:42

text? You probably won't put 10 in unprintable characters inside.

18:48

But if you import

18:49

text from

18:52

some software like database, you might get some characters that shouldn't be there. So we don't have text important from some database. But we created the text that has

19:04

character 11 which is rather crazy character, which shows like a small box here in in this l A. One.

19:12

And to remove it, we're just going to type clean

19:18

and a one,

19:22

and we get just ABC. So this unprintable character, which is character 11 is just being removed.

19:30

So next function has ah named that most people don't like to pronounce his concoct in eight,

19:38

and it basically joins more text strings into one.

19:42

And, uh,

19:45

example of this function?

19:55

Yeah, here it is.

19:56

It's difficult to type.

19:59

Is this one comma? This one? Come on, This one come out. This will cover

20:04

and clothes,

20:07

and we get this. So

20:08

it's very simple. Uh,

20:11

why would you need this function? It

20:15

It is problematic because you can do the same thing.

20:19

Yours again sign and the simple formula

20:25

like this,

20:26

Like this

20:29

and like this

20:32

and you get absolutely the same hours. However, sometimes it is good to have this formula, especially if you going to use my crow's. Then it's better to use this formula than something else.

20:47

Expansion, we're going to talk is exact

20:51

and exact

20:53

is

20:56

ah kind of

20:59

inquiry function that checks whether two strings are exactly the same or not, and returns to a false

21:07

and this is exactly the same means that it is case sensitive as well. So if you have

21:15

case like here,

21:18

so we say exact

21:22

off this comma, this

21:26

clothes plus center, we get false

21:30

and here we the way we have

21:33

exact match, including capital A at the beginning of the text get true.

21:38

So this function has used in checking things within

21:45

some queries and look up functions.

21:48

Ah, by itself it has no special value.

21:53

So next function

21:57

that we're going to talk about is find

22:00

and find finds Position off one text ring in other texting

22:07

and it has ah additional option of start number on. Let me show you how it works.

22:15

So if we have text, which is

22:18

a B A b a b A b,

22:22

and then

22:25

we want to find

22:32

find

22:33

Sorry, find.

22:37

And the text is

22:40

B A B sorry.

22:45

Within this text string

22:52

here,

22:53

close it press center will get too. So it starts at the position too.

22:59

But if you say OK, let's look from the turd character.

23:04

We'll get four because on third position is a so be Abie starts at fourth place in this strength,

23:15

this is very useful. It works pretty much like the find function and combined with some other functions, you can extract

23:25

some texts or find some text within the text ring eso when used in Mac rose or in some complex look up functions, it can be very, very useful.

23:40

Ah,

23:41

and one more

23:45

function that we're going to talk about is left

23:48

and left is

23:52

the function that returns specifying number of characters from the beginning of the text

23:59

So we can see

24:00

left

24:03

from

24:07

this and with dense a five

24:11

and we close it

24:12

and we get just Alfa.

24:15

Now imagine that we want to remove everything in this text

24:21

that is,

24:22

before the first space

24:25

We will days then see left Sorry,

24:30

left off

24:32

and then

24:33

we have this text

24:37

and then we will have fine which we just talked about

24:41

and then what we're looking for is

24:45

blank

24:48

within dis text here.

24:52

You don't need a starting them. So we look for the first appearance,

24:57

Ansari and we have to

25:00

subtract one

25:02

because we don't want this space which we're going to find to be in the final

25:07

result of this formula. So we want it

25:11

spaced,

25:14

be gone

25:15

and then look at this way. Look att This result we copied and we paste just the values.

25:22

Well, look at here. The reason of space at the end. So we have removed everything before the first base combined with some other functions. We can also remove

25:33

the part of the text from the middle, for example between two spaces

25:38

or sit in similar fashion

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