8 hours 33 minutes
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
from 0 to 4.
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.
Uh, if you look it
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
as an option.
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.
Sorry, we have ah
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
then we get
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
and we get seven.
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
with this number.
So, for example, if we have this number here,
which is 23.4567
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
0.5 to go here,
is it all here But here, If we would have, for example, four
then is going to be 22.5.
So this is sealing
is a function
that runs the positive number two the first hire, even integer and negative number two the first lower even integer
how it works
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
we go to the first.
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
and we have to
apprentices. But here it is.
So it goes down.
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
It goes down.
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.
So if we have,
Sorry, this one
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
instead of three,
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
quick change this.
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.
So if the number is
the higher so it's above.
If the last
digits are 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.
So if you do
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
If it's ah
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.
So if we
and through this function c
this number with two decimal points
and press it like this
and they attacked it,
So you see here
we got the classical rounding because it goes down and here it's not the classical rounding because it all again goes down.
Now trunk function
is slightly different. It has same functionality is integer
but you can defy number of decibels.
you say, drunk
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 if you say odd
So it was just compare it first to the
It has just one argument and see
and this number
then move it.
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,
you have to
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,
you get this forced
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.
Now the next
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.
But if you import
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 a one,
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?
Yeah, here it is.
It's difficult to type.
Is this one comma? This one? Come on, This one come out. This will cover
and we get this. So
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 like this
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
ah kind of
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
case like here,
so we say exact
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.
So next function
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
a B A b a b A b,
we want to find
And the text is
B A B sorry.
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.
and one more
function that we're going to talk about is left
and left is
the function that returns specifying number of characters from the beginning of the text
So we can see
this and with dense a five
and we close it
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,
we have this text
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
Data Analyst Excel
The Data Analyst Excel Test is a premium Cybrary assessment created by iMocha and intended ...
Monitor a Web App
In this IT Pro challenge, participants get hands-on experience activating server logging and using Application ...
Learn On Demand