馃攳
Easily Calculate Ratios in Excel - YouTube
Channel: Leila Gharani
[0]
Today, we'll take a look
at how we can calculate
[2]
and show ratios in Excel.
[4]
So for example, let's say
you want to show the ratio
[7]
of Managers to Team Members
or the ratio of Managers
[11]
to the total number of
people in your project team.
[14]
How can you calculate
and show that in Excel?
[17]
Let's jump in!
[18]
(upbeat dance music)
[22]
In this example,
[23]
I have 3 Managers and 7 Team Members,
[26]
and I want to show this as a ratio.
[28]
Well, a ratio in word
format is very simple.
[31]
It would be something like 3 to 7.
[34]
This means that for every three Managers
[36]
I have seven Team Members.
[38]
But a common way of showing
ratios is to use colon.
[42]
So 3:7 would be the answer here.
[45]
Now I want to get this with a formula,
[48]
but as you can see, the logic is simple.
[50]
All I'm doing is taking
the first value here,
[53]
putting it on the left-hand side,
[55]
and then the second value
goes on the right-hand side.
[58]
That's all a ratio is.
[60]
But one thing you'd want to consider
[62]
is to show a simplified
version of the ratio.
[66]
So for example, if instead
of 7 Team Members, we had 12,
[70]
we don't want to show 3:12,
although that's perfectly valid,
[75]
but it's much easier to
understand this ratio
[78]
if we simplify this.
[79]
We just have to find the
greatest common factor here
[83]
and divide each side with that factor.
[85]
So in this case, the
greatest common factor is 3.
[89]
3 divided by 3 would give me a 1,
[91]
and 12 divided by 3 would give me 4.
[95]
So I want to show this
ratio up here as 1:4.
[100]
Now, of course, we want to
show this with a formula,
[103]
and we're going to need a function
[104]
that calculates this
highest common factor.
[108]
That's the GCD function.
[110]
All we need to give it are our numbers.
[112]
So in this case, my numbers
are sitting together.
[114]
So I'm going to select the range,
[116]
but you can split this
with a comma as well
[118]
if you have your numbers
in different places.
[122]
Now, when I close bracket and press Enter,
[124]
I'm going to get a 3.
[126]
Why am I not seeing a 3?
[128]
Because my cell formatting
has switched to Time.
[132]
Let's switch it to General, and I get a 3.
[135]
Now, all I have to do
is take this number here
[140]
and divide it by the highest factor.
[143]
This would give me the left-hand side,
[145]
which is a 1.
[146]
To get the right-hand side,
[148]
I do the same thing, but
with the other number.
[150]
So my formula becomes this,
[153]
and now I want to add the colon,
[155]
so I'm going to use an &
and open the quotation,
[159]
put in the colon, close quotation,
[162]
and putting an & to
continue with this function.
[165]
Then I need to take this
number, so for the Team Members,
[169]
and divide this by the GCD of these two.
[174]
Right?
[175]
So this part of the
formulas stays the same.
[177]
Now, when I press Enter, I get 1:4.
[181]
If this was 7, it's 3 to 7.
[184]
Now what if you had to calculate
these numbers yourself?
[187]
So your data set didn't
give these numbers to you,
[191]
but instead you had your
data table set up like this.
[195]
You just had roles and names
and you need to calculate
[199]
the ratio of Managers to Team Members.
[201]
How do you come up with these
numbers in the first place?
[204]
Well, here we can just replace this
[207]
with the COUNTIF function.
[209]
First, we need the range.
[211]
That's the range where we have
Team Members and Managers,
[215]
and notice this is
formatted as an Excel table.
[218]
So I'm just selecting the column,
[220]
and I get the table annotation up here.
[223]
Next, all I need is my
criteria, which is Manager,
[227]
which I have in cell A3.
[229]
So closed bracket, press
Enter, and I get 5.
[232]
Now I'm going to copy and paste this here
[235]
because my references are dynamic.
[237]
I don't need to update anything.
[239]
So in this case, I have 5
Managers for 14 Team Members.
[244]
And because I'm using an Excel table,
[247]
all of this is dynamic.
[248]
If I add another Team Member
to this, notice I get 1:3.
[254]
If I needed to get the ratio
of Managers to total members,
[258]
I just need to add these up.
[259]
So instead of 15, I take 20.
[261]
(air whooshes)
(bomb exploding)
[263]
You can, of course,
combine all of these steps
[265]
and create a single formula.
[267]
It's just that your final formula
is going to become quite long
[270]
because we're referencing cell B3 and B4
[273]
on this side and on this side,
[275]
and B3 and B4 have formulas of their own.
[279]
Now, if you have Office 365,
[280]
you can take advantage of the
LET function and define names
[285]
for parts of this final
formula that are repeated.
[288]
This makes your formula easier
to read and more condensed.
[293]
So in this case, I would
want to define a name,
[296]
let's say for this
function and this function,
[299]
this way I don't have to
repeat it all the time
[302]
inside the final function.
[304]
Well, how do we do that?
[306]
Let's do it down here.
[307]
We're going to start off
with the LET function.
[309]
We need to define a name for the formula
[312]
or function or reference
that we want to reuse.
[315]
So I can call this Manager Team Member,
[318]
but because we're doing
dynamic references here,
[321]
let's do it the left and
the right side of our ratio,
[324]
so I'll call it left.
[327]
Name value 1 is the part of my formula
[330]
that I want to reuse later on.
[333]
So this can be a cell reference,
[334]
but it can also be a formula or function.
[337]
Now, I'm going to use the
COUNTIF function I had in there.
[340]
So I'll just quickly type it out.
[342]
My range is this, and my criteria is this.
[346]
I'm going to let this be dynamic.
[349]
Okay, so let's close the COUNTIF,
[351]
and now let's define another
name for the Team Member part,
[356]
so for the right-hand side.
[357]
I'm just going to impress Alt + Enter,
[360]
and let's just expand this.
[361]
We can see the formula better.
[364]
Right is going to be the name
of the second COUNTIF function,
[370]
which is this one.
[373]
And this, let's close the bracket.
[376]
Now, I'm going to do the calculation,
[378]
so Alt + Enter, and my calculation
[380]
is left&":"&right.
[387]
But remember we still
need the GCD formula.
[391]
So let's go ahead and do that.
[394]
It was left divided by gcd,
[397]
and then I have left and right.
[401]
And then this side we have divided by gcd
[405]
also left and right.
[408]
Right, so all we did here
[409]
is to replace the cell
referencing B3 and B4 with names.
[414]
I called them left and right,
[416]
and I defined what they
were with the LET function.
[420]
Now, the way you use the
LET function is up to you.
[422]
I could also add a separate
name for the GCD function here
[426]
so that I don't have to repeat this part
[429]
in my final function.
[431]
Once you're done press Enter,
[433]
and we get the same result back.
[435]
Let's test it out.
[437]
Let's quickly add another
Manager to this, and we get 2:5.
[443]
So that's how you can easily calculate
[445]
and show ratios in Excel.
[447]
I hope you enjoyed this tip.
[449]
Let me know if this is something
[451]
you might need in your reports,
[452]
or if it's something you're already using.
[455]
(light upbeat music)
That's it for today.
[456]
Thank you for being here.
[457]
And if you're new and haven't
subscribed to this channel,
[461]
do consider subscribing before you leave,
[463]
and I'll see you in the next video!
[465]
(upbeat dance music)
Most Recent Videos:
You can go back to the homepage right here: Homepage