馃攳
10 Excel Functions You NEED to KNOW as Engineers! - YouTube
Channel: Leila Gharani
[0]
This video is for all
new engineers out there,
[2]
especially mechanical engineers
who use Excel at work.
[5]
So make sure that you go through the
Excel functions that are show this video,
[9]
because it's quite likely that
you're gonna need them. Now,
[12]
the great thing is that these
functions work on all Excel versions.
[16]
And you're gonna be surprised
when you see some of these,
[18]
because I haven't covered of these
functions before in this channel.
[22]
And if you're wondering, how on earth
do I know what engineers need? I don't,
[27]
I'm not an engineer, but I
have an engineer in the team.
[31]
So he showed me how he uses Excel.
[33]
And I was actually very surprised about
some of the functions that he had.
[37]
It's quite different to the functions,
the finance professional needs. Now,
[41]
although you never know, because
it really depends on the situation.
[45]
So check it out. And especially
if you're an engineer,
[48]
because your life is
just gonna get easier.
[55]
Here's our sample database.
[56]
We have the bill of material level
and the part number for each level.
[60]
So this sense, a parent
child product relationship,
[64]
this part number belongs to this product.
[69]
These belong to this one. Now,
when we go further down here,
[73]
we come across these twos. They
don't belong to this three here.
[77]
They don't belong to the two or the three.
They belong to this product up here.
[81]
So you can and visualize this as
a hierarchy that looks like this.
[86]
Then here we have the part description
material category, the weight,
[90]
whether it was made or bought
and the quantity required.
[94]
Now let's start working
on this data set function.
[97]
Number one is the rep function. This
function allows you to repeat values.
[102]
And as often as you want,
[104]
so here we wanna get the indent part
number because remember part number is
[108]
organized like a hierarchy. We want to
visually see this using indentation.
[114]
So basically if you were gonna do it
manually, we'd be copying this over.
[118]
And then we're gonna take a look at
the bomb level and see if it needs
[121]
indentation or not. If it's on the top
of a hierarchy or not. So this one isn't,
[125]
so we don't need to do anything.
This one is in the first level,
[129]
just below this one. So we're gonna
add one indentation. This one, two,
[134]
these ones are also two. So
they belong to this one here.
[139]
Then this one is one,
this two and so on, right?
[143]
So you can do this manually, but you
can imagine you have a lot of data here.
[147]
You definitely don't
wanna do this manually.
[150]
So what you can do is
use the R E PT function.
[154]
It's a function that
repeats things. First,
[156]
you define the text that you wanna
repeat. So add a space in quotation marks.
[161]
Next, how many times do we wanna
repeat this? So let's say by default,
[165]
I wanna have three spaces.
I'm gonna add three,
[168]
but because it depends on the level here.
[171]
I'm gonna multiply this with
this number close bracket.
[175]
Now we're gonna combine this with
the am. Present with the part number.
[180]
When I press enter,
[181]
this doesn't have any indentation
because three times zero is zero.
[186]
But when I send this down, we get the
correct indentation for each level.
[191]
Next one, the trim function.
[194]
So sometimes your PLM software might
give you part numbers that are already
[198]
indented. Now these can give you problems
when you use looka formulas, right?
[203]
Because you have all these additional
spaces that you don't need,
[207]
and you wanna strip them off to do that.
[210]
You can use the trim function. All
you need is your value, close bracket,
[215]
press enter.
[216]
And it trims off any additional space
is that you have before anything
[221]
you have after. And in case you
have additional spaces in between,
[225]
they are trimmed off as well.
[228]
Next up we have Thelen
and substitute functions.
[231]
Sometimes your data extract might give
you the item level in this format,
[235]
and you wanna figure out the
bill of material number. Now,
[239]
the logic for this is that if
you just have one character,
[241]
you are on the top node.
So this is gonna be zero.
[245]
If you have one period in there
and one character after the period,
[249]
then you're in the first level. In
this case, you are in the second level,
[254]
actually in all these three cases,
you are in the second level here.
[258]
We're back to level one down
here because we have three
[263]
periods. Here. We are in level
three. That's the logic behind this.
[268]
But again, we don't
wanna do this manually.
[270]
We wanna figure out a
formula that does this.
[272]
That's where these substitute and
land functions come into play.
[276]
Now we're gonna start with substitute.
I can show you what that does.
[279]
First thing you need is the text.
[281]
That's the value that you wanna use
substitution on. So in this case,
[285]
it's this one, what's the old text
that we wanna substitute. Well,
[289]
I wanna take the period here and I
wanna substitute that with new text,
[294]
the new text is just nothing.
[298]
So basically I'm removing
that peer. When I close this,
[303]
I get to replace all instances
of the period with nothing.
[307]
So in this case, while I don't have
any period, I'm just gonna get a one.
[311]
But in these cases, notice
that period is gone. Now,
[315]
how can I get the appropriate
level number? Well,
[319]
I can use the length function to do that
because what the lens function does is
[324]
it tells me how many
characters this text has. Now.
[328]
This text is the result
of my substitute function.
[332]
So when I close bracket and presenter,
this one has just one character, right?
[336]
There was just a one in there. This
one will have two. This is three.
[339]
That last one is three as
well. Okay. This one is four.
[343]
And so on now to get the correct
bill of material note number,
[348]
I'm gonna use the lens function here,
[351]
grab the length of the item
level and deduct that from the
[356]
version where I remove the
period. Now, when I presenter,
[360]
I get the correct bill
of material. Number.
[364]
Next is the left function
with the left function.
[367]
You can grab the left side of
a longer text. So in this case,
[372]
we have the part identity and we
wanna split this into part number and
[376]
description. Part number should
be the number that we see here.
[381]
We can use the left function for
this. What we need is the text.
[384]
That's our value right here. Then we can
define the number of characters. Now,
[389]
if this is fixed, in your case, you
can just put that number in here.
[392]
So in this case,
[393]
I wanna extract the first 10
characters I to 10 close bracket
[398]
presenter, and I have my part number.
[402]
Next function is fine.
[405]
So let's assume that our part number
could have different characters.
[409]
The logic here is to grab everything
that comes before the comma.
[413]
You can use the find function to do that.
[417]
So instead of defining
that, we want 10 characters.
[421]
We're gonna look for the
comma using defined function.
[426]
The text that we wanna find is a comma.
[429]
I'm gonna put it in quotation marks
within text. That's our cell right here.
[433]
Start number by default is gonna start
looking from the first character are just
[438]
gonna leave that empty
close bracket, press enter.
[442]
And now we get our part
number, including that comma,
[445]
because what find does,
[447]
if I debug this to show
you the underlying value,
[451]
we can see exactly how many
characters is behind this.
[454]
I'm gonna press F nine and we can see 11.
[457]
That means that the comma is
the 11th character in this
[462]
text here. So I'm just gonna
press control Z to go back.
[466]
What we need to do is deduct one
from this. So minus one presenter,
[472]
and now we have our part numbers.
[475]
Now let's take a look
at the right function,
[478]
the right works in a similar
way to the left function,
[481]
except we get to strip out the right
side of the text. So in this case,
[486]
if I start off with right, my
text is right here and I say,
[491]
I wanna get 1, 2, 3, 4, 5, 6, 7,
[494]
the last seven characters.
I'm gonna end up with trailer,
[498]
but this is not thing I can pull down
because I have different number of last
[503]
characters. What I wanna do is
figure out where that first comma is,
[508]
and then strip out everything
that comes after that first comma.
[512]
I wanna make this seven here, dynamic.
[515]
Now I can use the lens function that
we saw before to get the total care
[520]
characters that we have right here.
[523]
Once we have to total characters,
[525]
we are going to deduct the number
of characters up to that comma here.
[530]
So we are gonna use the find
function. Look for the comma.
[535]
Look for it right here.
[537]
And start number by default is to start
looking from the beginning and we're
[541]
gonna close the bracket here, press
enter. We get trailer for this.
[546]
Does it work?
[547]
If I send this down and it works next,
[551]
look up function. This is one of the
original looka functions in Excel.
[556]
Now it syntax is simple, but you can
make this function a bit complex,
[561]
especially if you wanted to do
complex things. So in this case,
[565]
what we wanna do is find
the immediate parent part.
[568]
What this means is that for
this first part number here,
[571]
where we are on the top node,
[573]
doesn't have a parent who Don
want anything for this one.
[576]
The immediate parent of this is
this right here. Then for these,
[581]
the immediate parent
is this one right here.
[585]
Now it's not always the label that's above
it because take a look at these ones.
[590]
The immediate parent is not
three because that's below it.
[594]
Two is adjacent to it.
[596]
So we have to go all the way back
up to find the immediate parent.
[600]
And that's this one right here, right?
[603]
So that's ultimately the
result that we wanna get,
[605]
but we wanna do it with a function. Now,
[608]
the look up function
here is a bit complex.
[610]
So if you really wanna understand how
this works comment below and I'll consider
[615]
making it video about this, you're
gonna start off with lookup. Uh,
[620]
if you're wondering why
I'm writing it here,
[622]
that's because the first cell
here doesn't have a parent.
[626]
So if I write my formula here, I'm
gonna end up with an error anyway,
[630]
just to make sure I have everything
correctly done with the syntax and so on.
[634]
I write it here and then we can
copy it down and up as well.
[638]
So the look of value is gonna be
a two. I'll explain in a second.
[641]
Why the look up vector
is one divided by we're
[646]
gonna highlight this cell from the
beginning to right where we are, right?
[651]
So to right where we're
writing the formula.
[653]
But because we want this range to expand
as we pull this down so that we can
[657]
always look back to see where the
immediate parent was. I'm gonna fix the,
[661]
a two reference by pressing F four
and putting the dollar signs there.
[666]
Now I wanna check if
this equals this minus
[671]
one. Now all of this needs
to be in brackets. Okay?
[676]
So that's my look up vector.
And my result vector is this.
[680]
And again, I have to be consistent
with the look up vector reference.
[684]
I'm gonna fix the B2 reference
by pressing F four. Now,
[689]
when I close bracket and press enter,
I get the immediate parent right here.
[693]
Does it work if I send this
down? Is this parent correct?
[698]
It's the value right here, right?
So this works perfectly well.
[702]
Now just a quick explanation of why
a two, well, what this part returns.
[707]
If I press F nine to debug this,
[710]
you see that we get one or an error.
[713]
Two is just a value
that's higher than one.
[717]
It could be any value
that's higher than a one.
[720]
So we're gonna get the same
answers. If you put a three,
[723]
if you put a four or anything that
higher you'll get the same result.
[729]
Next up is the if error function.
[732]
So I wrote the look of function
starting from the third row here,
[735]
but it would actually be correct to
write it from the first row and then
[740]
send this down. You wanna have a
consistent formula throughout your column,
[745]
but you might end up with errors. Whenever
your level is zero to overcome this,
[750]
you can use the, if error functions.
[753]
So you're gonna start off
with if error open bracket,
[756]
your value is in this case,
the formula that we have here,
[760]
and then we can define
the value we wanna get.
[763]
In case we have an error and I just
wanna put dash here in quotation
[768]
marks, closed bracket presenter.
[770]
Now I either get the dash or I
get the immediate parent part.
[776]
Now let's take a look at the if function.
[779]
So let's say we wanna check whether
we are at a top level or not.
[784]
So whether the bill of material level
is zero or not here, we can use the,
[789]
if function to do this check and you
can use it to do any type of checks that
[793]
are based on <affirmative>
and logical test.
[795]
So our logical test here is to take this
number and see, is it equal to a zero?
[800]
Now, if it is true. So
if it is equal to a zero,
[804]
what do we want returned? Let's
say, I want, yes, returned else.
[809]
What do I wanna return? If it's false,
[811]
I wanna return and no
close bracket percenter.
[815]
Now this way I can quickly identify
whether I'm at a top level or not
[820]
as a bonus.
[821]
Here's a formula that brings together
three of our important functions into
[825]
one function. Here. We
calculate extended quantity now,
[829]
just so that we can see better,
[831]
I'm gonna drag it and
drop it right here beside.
[835]
So what this does is it calculates
the quantity of each part based
[841]
on the quantity that is
needed by the parent as well.
[845]
So let me explain for this
part here. The quantity is two.
[850]
The parent of this one
has a quantity of four.
[853]
So the extended quantity is two
times four. It's eight. Now,
[857]
when it comes to this
one, the quantity is two.
[861]
The extended quantity is two times eight.
[864]
That's 16 to get this
to calculate correctly.
[868]
We've used a similar approach to the
look up approach with here to get the
[872]
immediate parent part. We have
if error for error, hand link,
[876]
and in case we happen
to be at the top level,
[879]
we are just gonna return the quantity
that we have in the age column.
[884]
I hope you found some value
in this session, whether
you're an engineer or not.
[888]
So before you leave, let
me know in the comments,
[891]
what function you really liked.
Thank you for watching subscribe.
[895]
If you are unsubscribed and I'm
gonna see you in the next video.
Most Recent Videos:
You can go back to the homepage right here: Homepage