Calculate Ratio in Excel

Video Transcript: Calculate Ratio

When you want to compare numbers, one way to do that is by using a ratio such as four to 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 400 and 300. To show those as a ratio, we don't want to show 400 to 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 100 into 400 four times and into 300 three 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'd need formulas to calculate those. So I'm going to remove these.

Use GCD Function

There is a function in Excel that will calculate the Greatest Common Divsor.

• So in this cell, I'm going to type equals GCD
• 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.

• And I'll double click on this fill handle.

And that shows me the greatest common advisor for each of these other screen dimensions.

Divide the Results

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 width divided by our greatest common divisor,
• and that's a four,
• double click to copy that down.
• And the same thing in this cell. We're going to divide the height by our greatest common divisor.
• And so that's three and there are 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 fill that down.

Create a Single Formula

Now instead of showing that in four separate cells, I could do it all in one formula.

And if we look at the final number here, we're using E4 colon F4. So our first number is the width divided by the greatest common divisor. The second number is the height divided by the greatest common divisor.

• So in this cell type, an equal sign,
• then the width divided by,
• and then the formula for the greatest common divisor,
• and then our colon,
• and then the height divided by 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.

Use TEXT and SUBSTITUTE Functions

Another way we can show ratio is by using the TEXT and SUBSTITUTE functions.

So here I've got the same numbers, and for this technique, we're going to be dividing the numbers and then showing them as a fraction, and finally replacing the slash in the fraction with a colon.

So the first step will be division.

• I'll click here equals the width, divided by the height,
• and 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 cell.

• So on the Home tab, I'll click the Number Format button.
• If I look down the list of categories, here's fraction, and I can show up to two digits.
• I'll select that one.

So that shows it 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 want it to show four thirds instead of one and a third.

• So I'm going back into the number format and 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.

• And I'll double click here to fill it down.

Format with TEXT and SUBSTITUTE

In the custom formatting, we can't replace that slash with a colon because the colon is used for times, so we won't get a correct result. But we can use the TEXT function to create a custom custom format, and then SUBSTITUTE the slash with a colon in this cell.

I'm going to use the TEXT function to create that custom format type.

• An equal sign TEXT,
• And we want to divide the width by the height
• and then a comma.
• And now we have to show how we want to format that.
• And we're going to use something similar to that custom format that we saw.
• So in double quotes, number sign slash,
• then I'll type five or six 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

• equal SUBSTITUTE open bracket.
• And I'll click on this cell where we have our calculation.
• And the old text is the slash.
• So in double quotes, I'll type a slash comma
• and the new character we want, the new text is a colon.
• So in double quotes, I'll type a colon, close the brackets, and press Enter.

And there's our ratio.

• Double click to fill that down.

And we get the same ratios we had on the other sheet.

Create Single Cell Formula

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 TEXT formula dividing the width by the height,
• our custom format that we want to use, which is the fraction.
• And in the SUBSTITUTE, we want to replace the slash with a colon.

And there, all in one formula, is our ratio using TEXT and SUBSTITUTE.

• And I'll just fill that down

Use Either Formula

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