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:
- Go to Cell D4.
- Put the following formula based on division and ampersand symbol.
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:
- Hit the Enter button and drag the Fill Handle icon.
We get the same result.
The disadvantage with this method is- we do not get integer numbers on both sides.
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.
- We add a column named GCD to the dataset.
- We will calculate GCD now. Put the following formula on Cell D4.
- 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.
- 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:
- 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.
- Put the following formula on Cell D4.
- 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.
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.
- Go to Cell D4 and put the formula below.
- 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.
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.
- 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)