let's continue this lesson.
We do function called Arabic,
and it has just won the argument, which has to be in text form,
text format and Arabic returns, Arabic numerals from Roman numeral. So, uh, it's, Ah,
very simple way to do it.
And the other correlated function
is Roman, and he turns Roman numeral from Arabic.
But because there are five different forms off Roman rose, there is an option for form, which can be
And zero is a classic Roman form of doing that
where and it goes to different stages till it reaches option for which represents very, very simplified version of writing Roman numerals.
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.
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
hundreds so it's C m meaning,
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.
slightly different way of fighting it l represents 50. So, uh ah,
it's a complex thing. I don't want to go into explanation because these things they can get very, very
complex, especially if we go with the
So it changes a lot.
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,
with some other numbers which are not so close to two
hundreds of or thousands,
completely same results regardless off options.
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
comparison of how Arabic works with different forms of Roman numbers and it always gets the same result. So here we have
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
Ah, the next function is decimal
and the syntax of this mill is very simple. We have a number and we have a form.
ah number and we have Ray Dicks or base in which the number is presented and this number can be text or number. But
in general, we should be in text format
because if you go with bases that they're higher than 10
need for letters in the representation of numbers. So it has to be text.
here is the explanation of it. So f in X additional means that we're having
number A, which represents 10 and it's in place on on tens. So it's 10
multiplied by 16. So it's 160 F represents 15. So it's 50 90 to 160 we get
175. Here we have a number with the base eight
and then we calculated. So it means one times whatever.
And let's not go into details is just there, presenters, If we would put here
This can also be extra decimal number.
And this is what we get as a result.
And here is the typical binary
representation. So, for example, binary number seven is 111
Ah, So this is something that you can
use if you want to transfer numbers from different bases to already exes to the decimal number.
And now we move to a very complex
matter off rounding numbers
and ah, let's go first with the ceiling
and the function ceiling
it rounds the number to the first larger multiplier of significance.
Now it sounds complicated, but let me explain to you what it means.
Significance is what you put here,
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,
that ends with 0.5. So this
means that ceiling will around the number to the first higher number
divisible without remains
So, for example, if we have this number here,
and it's minus its ah,
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
Here also weaken Do with
ah, uh, for example,
based one or significance. One anyth rounds to the first hire. Because why? Because 23 is higher or larger number than minus 23.6735
same goes with the positive numbers. So if we have
is it all here But here, If we would have, for example, four
then is going to be 22.5.
that runs the positive number two the first hire, even integer and negative number two the first lower even integer
first lower negative number Ah,
uh, even number compared to number minus 23.6735 is
minus 24 because when we talk about negative was minus 24 is smaller than minus 23.6 by and when we talk about
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.
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
and then we take this number.
See, you always have to.
They care that for example, we put here five
apprentices. But here it is.
So here is 23.5. Here's 23. If we move this function around, we get
So it's minus 24. Why? Because minus 23.67 is closest to the minus 24 so on and so on
Uh, minus 23.6735 is obviously closest to minus 23.5.
But because this is floor, it goes to the lower number every time.
is where simple one integer just returns the Britain's number to the nearest integer.
and we did. It's 23 if we talked with negative numbers.
Integer off minus 23.6 and 35 is minus 24. And if you look at this function, it works rather similar to floor.
So here, if you put here the number seven
this changes the changes. But
interviewer doesn't. Why? Because here we have 0.5. So if you put one here,
we got the same functionality in floor. We get the same functionality like with integer so
We get the same results.
Now Next function is round,
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.
That means that the result off round doesn't have to be in danger.
And let's it's like this.
Have this and then we put here zero
and we get 23 so it works
in the usual rounding manner, so it rounds to the closest number.
Now, if we put here to decimals,
we get the decimal number
and then it gets. It performs the same rounding
logic like when it's an integer.
the higher so it's above.
five, then it goes up. So it goes to four points point for six
and ifit's below. It goes
the other way around,
so it's classical rounding.
does the same thing,
but it always goes to the high number.
this number puts also two decimals,
which is something that is usually used in calculations. This is what we get. It always goes up. So if it's
4567 it's goes to 46. If it's for the miners 67 in those 2 68
if it's 78293 goes to 79
0.21247 it goes up to 1.3. Although the classical around and would go to 12
round down around down does the same thing. But it goes to the Lord.
and through this function c
this number with two decimal points
and press it like this
and they attacked it,
we got the classical rounding because it goes down and here it's not the classical rounding because it all again goes down.
is slightly different. It has same functionality is integer
but you can defy number of decibels.
out of this number and then we put
and we get absolutely the same result is integer
But if we put one decimal,
it'll round to one decimal.
It actually is goingto
the same. Like we would, um,
multiplied the number by 10 do the integer and then divided by 10. This is the result
and in the end, the odd
that is absolutely similar way like even function.
But the result is on number.
So it was just compare it first to the
It has just one argument and see
So the result is always odd number and its rounded accordingly.
So this is how the rounding can be done in excel there quite a lot off these functions,
and you need to know which one to use when you do your surrounding.
So far you have seen, uh, how math functions can help you do quite
complex mathematical calculations.
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.
The reasons for that might be very various. For example, you need to import
text data from other programs and they may not be formatted in the way you want them.
Uh, people entering text main making steaks with case, for example, they can enter
to capital letters in the name extent of one. They can, uh,
at extra spaces where they shouldn't be.
So all these things that when you type, for example, in inward, these things usually get off the corrected. Here in excel,
pay attention to these things. Also, if you exporting data to some other programs, you might be required to reformat your daytime too specific
type of for mating of texts in order for that
program to accept it. For example, if you have ah, no name and surname in in two separate cells
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.
So let's start with functions. First function is char,
and it basically returns. A single character corresponding to ask echoed off their argument number
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
like in this example here
Ah, line break within the text.
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,
uh, added character 10 s key code 10
and this character represents the line break.
Now, when you look a TTE this, you don't see much of it.
But, uh, if you form itself so that the text is wrapped,
enter or within the middle off the text.
Why would you need that? For example, in some tables, you would like in order to save space. Because there are so many cones.
You want the text to be broken in the middle in two lines instead of one line
so he doesn't overlap, or
there there should be a decent space between,
uh, explanations of calm. So in this case,
you would definitely need that.
function we're going to mention is clean
and clean removes none printable characters from the text string.
And why would you need that? When you build your own
text? You probably won't put 10 in unprintable characters inside.
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
character 11 which is rather crazy character, which shows like a small box here in in this l A. One.
And to remove it, we're just going to type clean
and we get just ABC. So this unprintable character, which is character 11 is just being removed.
So next function has ah named that most people don't like to pronounce his concoct in eight,
and it basically joins more text strings into one.
example of this function?
It's difficult to type.
Is this one comma? This one? Come on, This one come out. This will cover
it's very simple. Uh,
why would you need this function? It
It is problematic because you can do the same thing.
Yours again sign and the simple formula
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.
Expansion, we're going to talk is exact
inquiry function that checks whether two strings are exactly the same or not, and returns to a false
and this is exactly the same means that it is case sensitive as well. So if you have
off this comma, this
clothes plus center, we get false
and here we the way we have
exact match, including capital A at the beginning of the text get true.
So this function has used in checking things within
some queries and look up functions.
Ah, by itself it has no special value.
that we're going to talk about is find
and find finds Position off one text ring in other texting
and it has ah additional option of start number on. Let me show you how it works.
So if we have text, which is
Within this text string
close it press center will get too. So it starts at the position too.
But if you say OK, let's look from the turd character.
We'll get four because on third position is a so be Abie starts at fourth place in this strength,
this is very useful. It works pretty much like the find function and combined with some other functions, you can extract
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.
function that we're going to talk about is left
the function that returns specifying number of characters from the beginning of the text
this and with dense a five
and we get just Alfa.
Now imagine that we want to remove everything in this text
before the first space
We will days then see left Sorry,
and then we will have fine which we just talked about
and then what we're looking for is
within dis text here.
You don't need a starting them. So we look for the first appearance,
Ansari and we have to
because we don't want this space which we're going to find to be in the final
result of this formula. So we want it
and then look at this way. Look att This result we copied and we paste just the values.
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
the part of the text from the middle, for example between two spaces
or sit in similar fashion