The ratio is one of the common ways to compare two values. It indicates the amount of difference between two values from each other. In Excel, we can calculate this ratio. But there is no direct formula for that. Here, we will show how to calculate the ratio by customizing different functions.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Methods to Calculate Ratio in Excel
We will calculate the ratio in Excel using 4 different methods. We will compare the length and width of 5 wheat fields of a farmer.
1. Calculate Ratio with the Help of Ampersand (&) Symbol
In this section, we will use the Ampersand (&) symbol to determine the ratio. Before using the Ampersand symbol, we will divide one value by the other and then add the Ampersand symbol with the division inside the same formula. Follow the steps below:
📌 Steps:
- Go to Cell D4.
- Put the following formula based on division and ampersand symbol.
=B4/C4&":"&1
Here, we divide the length by width. Then, add the Colon(:) symbol using the ampersand. Lastly, add 1 as the right-side value of the ratio.
- Then, press the Enter button.
- Now, pull the Fill Handle icon downwards.
We’ve got the ratio.
- We can slightly modify the formula. And the formula becomes:
=B4/C4&":"&C4/C4
- Hit the Enter button and drag the Fill Handle icon.
We get the same result.
Note:
The disadvantage with this method is- we do not get integer numbers on both sides.
Read More: How to Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)
2. Using Excel GCD Function
The GCD function finds out the biggest common divisor of multiple numbers. This GCD can divide that number without any reminder. In this section, we will find out the GCD of two numbers and then use this GCD to calculate the ratio.
📌 Steps:
- We add a column named GCD to the dataset.
- We will calculate GCD now. Put the following formula on Cell D4.
=GCD(B4,C4)
- Double click the Fill Handle icon to get values for the rest of the cells.
We get the GCD for all the data cells. Now, we will use this GCD to calculate the ratio.
- Go to Cell E4 and write the following formula.
=B4/D4&":"&C4/D4
- Drag the Fill Handle icon now.
We get the ratio for all the data. One thing that needs to mention is that the ratio values are integers.
Note: If you don’t want to add an extra column, then use the following formula in Cell D4:
=B4/GCD(B4,C4)&":"&C4/GCD(B4,C4)
Read More: How to Calculate Ratio Between Two Numbers in Excel (5 Ways)
Similar Readings
- Convert Percentage to Ratio in Excel (4 Easy Ways)
- How to Calculate Odds Ratio in Excel
- Convert Ratio to Decimal in Excel (3 Handy Methods)
- How to Calculate Male Female Ratio in Excel (3 Suitable Methods)
- Calculate Ratio Percentage in Excel (4 Easy Methods)
3. Combining TEXT and SUBSTITUTE Functions
In this method, we will use the combination of the TEXT and SUBSTITUTE functions to calculate the ratio.
The TEXT function converts a value of text to a specific number format.
The SUBSTITUTE function replaces existing text with new text in the text string.
📌 Steps:
- Put the following formula on Cell D4.
=SUBSTITUTE(TEXT(B4/C4,"##/##"),"/",":")
- Press the Enter button to get the ratio.
- Pull the Fill Handle icon to get the result for the rest of the cells.
Here, we get the ratio easily.
Read More: How to Calculate Current Ratio in Excel (2 Suitable Examples)
4. Calculate Ratio with ROUND Function
In this section, we will use the ROUND function to get the ratio. This ROUND function is used along with the Ampersand symbol.
📌 Steps:
- Go to Cell D4 and put the formula below.
=ROUND(B4/C4,1)&":"&1
- Hit the Enter button.
- Now, drag the Fill Handle icon downwards.
Here we used the ROUND function because this will fix the digits after the decimal point to a certain number.
We can also use this formula and it will return the same value.
=ROUND(B4/C4,1)":"&C4/C4
Read More: How to Graph Ratios in Excel (2 Quick Methods)
Conclusion
In this article, we described 4 methods to calculate ratio in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
Related Articles
- How to Calculate Compa Ratio in Excel (3 Suitable Examples)
- Calculate Average Ratio in Excel (2 Simple Ways)
- How to Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)
- Calculate Sharpe Ratio in Excel (2 Common Cases)
- Debt Service Coverage Ratio Formula in Excel
- How to Calculate Sortino Ratio in Excel (2 Methods)
Great tutorial, yet I am stuck at trying to apply a conditional formatting of color scales to a column of ratios – in the same way I could with a column of values.
Any idea if this is possible and how to achieve?
I did try finding a way to dynamically copy the colors from a range of the values the ratios represent, and where the conditional formatting did work as I wished. Unfortunately, I couldn’t find a way to dynamically copy conditional format generated colors either.
Would be pleased to figure out a way to do either! (but the first would obviously be better). Also, FWIW, I am working in Excel 2007.