Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

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

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor