Home > Formulas > Text > Ratio Calculate Ratio in ExcelHow to use Excel functions to show a ratio, such as 4:3 or 8:5. To calculate ratio in Excel, you can use the GCD function. Or use TEXT and SUBSTITUTE functions to calculate proportion in Excel. |
Video: Calculate Ratio in ExcelTo calculate a ratio between 2 numbers in Excel, you can use the GCD function (Greatest Common Divisor) or use the TEXT and SUBSTITUTE functions. Watch this video to see how to use these formulas to calculate proportion in Excel.
Written instructions are below the video, as well as the full video transcript. Video Timeline
Video TranscriptIf you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript Video Transcript: Calculate RatioWhen 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.
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 FunctionThere is a function in Excel that will calculate the Greatest Common Divsor.
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 that shows me the greatest common advisor for each of these other screen dimensions. Divide the ResultsSo 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.
And now to get the ratio, I just want to show this number and this one with a colon between them.
So we can now see the width and height as a ratio and just fill that down. Create a Single FormulaNow 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.
And we get the same ratio all in one cell instead of the four separate cells.
So you can either break it down into separate steps or do it all in one formula. Use TEXT and SUBSTITUTE FunctionsAnother 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.
Now to show this as a fraction, I'm going to format the cell.
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.
And now it shows what I want, which is that four thirds.
Format with TEXT and SUBSTITUTEIn 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.
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
And there's our ratio.
And we get the same ratios we had on the other sheet. Create Single Cell FormulaAnd again, we could do this all in one formula, instead of breaking it out into different cells.
And there, all in one formula, is our ratio using TEXT and SUBSTITUTE.
Use Either FormulaSo you can use either formula. The only note is that:
|
Test the GCD FunctionIn this simple example, width numbers are in column B and Height numbers are in column C.
In column D, to see the largest number which will divide equally into the numbers in B4 and C4, enter this formula in cell D4:
The GCD result is 100. That is the largest integer that divides both numbers (300 and 400), without leaving a remainder Create the Ratio FormulaTo 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. To see the ratio, enter this formula in cell E4:
The result is 4:3 -- the ratio for those screen dimensions. Cell ReferencesIn the above example ratio formula, I used a relative reference to each cell -- B4 and C4. That way, the row numbers in the references will adjust automatically, when the formula is copied down to the rows below. If you use an absolute cell reference, like $B$4 and $B$4, the dollar sign locks the row numbers and column letters. Those won't change if you copy the formula down or across. |
Video: Ratio Formula with TEXT and SUBSTITUTEIn this video, see how to create a ratio formula in 3 steps, using division, TEXT function and SUBSTITUTE function. There is another example, below the video, with written steps. Video Timeline:
|
Video: Introduction to RatiosFor a short introduction to Ratios, you can watch this video from the Math with Mr. J channel on YouTube. It shows two examples for calculating a ratio, with a pencil, instead of Excel. Understanding what a ratio is, and the different ways you can show it, might make it easier for you to understand how the GCD function helps create a ratio in Excel. |
Get the Sample FilesScreen Dimensions: Download the zipped sample Excel Ratio Formulas file with the list of screen dimensions and ratio formulas Ratio Formula 3 Step: Download the zipped sample Excel Ratio 3 Step Formulas file with ratio formula steps for Divide, Fraction and Ratio. Excel Function Tutorials |
Last updated: February 28, 2023 3:56 PM