Calculate Ratio with Excel Formulas

http://www.contextures.com/excelformu... Visit this page to download the sample file.

To calculate a ratio between 2 numbers in Excel, you can use the GCD function (Greatest Common Divisor) or use the TEXT and SUBSTITUTE functions.

In the first example, to calculate the ratio, the width will be divided by the GCD and the height will be divided by the GCD. A colon will be placed between those two numbers.

In the second example, o calculate the ratio, the formula will divide the width by the height, and format the result as a fraction. Then, the slash will be replaced with a colon, to create the ratio.

0:16 Ratio introduction
1:20 GCD formula
4:03 TEXT and SUBSTITUTE formula

Watch this video to see how to use these formulas to calculate the ratios in Excel.

---

Closed Caption:

when you want to compare numbers one way
to do that is by using a ratio such as
for two three or five to four
there's no function in Excel that does
this but we can use other functions to
create a ratio now here we have numbers
403 hundred to show those as a ratio we
don't want to show 402 300 we want to
break that down to the smallest numbers
possible if we look at these two numbers
the first step is deciding what is the
biggest number that could go into each
of these two numbers and divide evenly
just by looking at them they're both
hundreds i would guess that a hundred
would go into both of these numbers
evenly and that's probably the biggest
number that we can use so that is the
greatest common divisor i'm just going
to type that here if I look at 400 i
could divide 102 404 times and into 303
times so our ratio would be four to
three so it's easy to look at that one
and figure it out without any formulas
in excel but these are some screen
dimensions and it's not as easy to look
at those numbers and decide so we need
formulas to calculate those are going to
remove these there is a function in
Excel that will calculate the greatest
common divisor so in this selling going
to type equals G C D and then click on
the first number type a comma and the
second number and close the bracket when
i press enter it shows that yes I was
correct in my guess that 100 is the
greatest common divisor for those two
numbers i'll double-click on this fill
handle that shows me the greatest common
divisor for each of these other screen
dimensions so now that we have this
number we can do the next step which is
how many times can we divide this number
into this one and to do that we can use
the division operator so i'll type here
equals the wind / our greatest common
divisor and that's a 4 well
double click to copy that down the same
thing in this cell we're going to divide
the height by our greatest common
divisor and so that's three there the
rest and now to get the ratio i just
want to show this number and this one
with a colon between them start with an
equal sign click on the first number to
join characters in a string i'm going to
use the ampersand character then in
double quotes i'll type a colon another
ampersand and the second number so we
can now see the width and height as a
ratio and just feel that down instead of
showing that in four separate cells i
could do it all in one formula if we
look at the final number here we're
using e4 colon f4 so our first number is
the width / the greatest common divisor
second number is the height / the
greatest common divisor so in this cell
type an equal sign then the width / and
then the formula for the greatest common
divisor and then our colon and then the
height / the greatest common divisor and
press ENTER and we get the same ratio
all in one cell instead of the four
separate cells i'll double-click to fill
that down and you can see the results
are all the same so you can either break
it down into separate steps or do it all
in one formula another way we can show
ratio is by using the text and
substitute functions so here about the
same numbers for this technique we're
going to be dividing the numbers and
then showing them as a fraction and
finally replacing the / in the fraction
with a colon so the first step will be
division
click here equals the width / the height
we can see that as a number with decimal
places to get that as a fraction i can
just refer to that division cell now to
show this as a fraction
I'm going to format the cells so on the
Home tab will click the number format
button if i look down the list of
categories here's fraction and I can
show up to two digits will select that
one so it shows that as one and a third
so that's really three thirds and
one-third which would be four thirds and
that's the ratio that we want but we
wanted to show four thirds instead of
one and a third so I'm going back into
the number format go down to custom and
it's going to show us here how it has
formatted that so I don't want a number
and then the fraction so i'm going to
delete that part of it
click OK and now it shows what i want
which is that four thirds will double
click here to fill it down in the custom
formatting we can't replace that / with
a colon because the colon is used for x
so we won't get a correct result but we
can use the text function to create a
custom format and then substitute the /
with a colon in this cell i'm going to
use the text function to create that
custom format type an equal sign text we
want to divide the width x the height
and then a comma and now we have to show
how we want to format that we're going
to use something similar to that custom
format that we saw so in double quotes
number sign / then i'll type 5 or 6
number signs as the lower part of the
fraction just so it can accommodate any
fractions that we create
double quote close the bracket and
there's our fraction so that's very
close to what we want but we want to see
a colon here instead of a slash so in
this cell i'm going to use the
substitute function which will replace
one thing with another equals substitute
Open bracket will click on this cell
where we have our calculation the old
text is the / so in double quotes type
backslash comma and the new character we
want the new text is a colon so in
double quotes all typed colon close the
brackets press ENTER and there's our
ratio double-click to fill that down and
we get the same ratios we had on the
other shape and again we could do this
all in one formula instead of breaking
it out into different cells here we'll
start with an equal sign and substitute
and then our texts formula dividing the
width x the height or custom format that
we want to use which is the fraction and
in the substitute we want to replace the
/ with a colon and they are all in one
formula is our ratio using text and
substitute I'll just feel that down so
you can use either formula the only note
is that in older versions of Excel Excel
2003 and older you have to have the
analysis toolpak installed in order to
use the greatest common divisor and in
any version of Excel you can use
substitute and text for more excel tips
and tutorials and to download the sample
file for this video please visit Michael
textures website at
www.carandtruckremotes.com