Ratio is the comparison of two quantities and is expressed by a colon (:). For example, if the ratio of Class 1 and Class 2 in a school is 2:1, it represents that the number of students in Class 1 is twice the number of students in Class 2 in that school. This article shows how to calculate Ratios in Excel. There may be some cases when you have to represent data in Excel in the form of a ratio. This article will show you how to do that.
How to calculate Ratio in Excel
We will show you the following two methods to calculate the Ratio in Excel:
- By using the GCD function
- By using the SUBSTITUTE and TEXT functions
Let’s start.
1] Calculate ratio in Excel by using the GCD function
GCD stands for Greatest Common Divisor. It is also called HCF (Highest Common Factor). In simple words, it is the greatest number that can divide a particular set of numbers. For example, the Greatest Common Divisor of the set of numbers 8, 12, and 20 is 4, as 4 can divide all these three numbers.
We will use this GCD function in Excel to calculate the ratio. We have created a sample data shown in the above screenshot. First, we will calculate the GCD, then we will use this GCD value to calculate the ratio.
Select the cell in which you want to display the GCD. In our case, it is the cell C2. Now, type the following formula and hit Enter.
=GCD(first value,second value)
In the above formula, replace the first value and second value with the cell numbers. For example, in our case, the formula looks like:
=GCD(A2,B2)
Enter the correct cell number, otherwise, you will get an incorrect result or an error. Now, copy the entire formula to the remaining cells using the Fill Handle.
Now, we will use this GCD value to calculate and display the ratio in our targeted cells. Use the following formula to calculate and display the ratio in Excel.
=first value/GCD&" : "&second value/GCD
In the above formula, we have divided the first and the second values by GCD and separated both of these values with the colon (:). In our case, the formula looks like this:
=A2/C2&" : "&B2/C2
In the above formula, A2 is the cell containing the first value, B2 is the cell containing the second value, and C2 is the cell containing GCD. Now, copy the formula to the remaining cells using the Fill Handle feature.
You can directly use the GCD formula in the ratio formula instead of calculating and displaying the GCD values separately. For example, in the following formula, you have to replace the GCD with the actual GCD formula:
=first value/GCD&" : "&second value/GCD
Hence, the formula will become:
=first value/GCD(first value,second value)&" : "&second value/GCD(first value,second value)
Replace the first and second values in the above formula with the correct cell numbers. In our case, the complete formula looks like this:
=A2/GCD(A2,B2)&" : "&B2/GCD(A2,B2)
After applying the formula to one cell, copy it to the remaining cells by using the Fill Handle. This is how to calculate ratios in Excel using the GCD function. Below, we have explained the method to calculate the ratio in Excel by using the SUBSTITUTE and TEXT functions.
Read: How to calculate Median in Excel
2] Calculate ratio in Excel by using the SUBSTITUTE and TEXT functions
Here, we will use the TEXT function to display the result in a fraction, then we will replace the fraction with the ratio by using the SUBSTITUTE function. Here, we will take the same sample data to show you.
If we divide the value in the cell A2 by the value in the cell B2, the formula is:
=A2/B2
This will display the result in decimal. Now, to show this result in fractions, we will use the TEXT function. Hence, the formula becomes:
=TEXT(A2/B2,"####/####")
In the above formula, you can increase # to increase the accuracy. For example, if you want to show fractions for up to five digits, you can use five # symbols.
Now, we will create the formula to replace the “/” with “:” by using the SUBSTITUTE function. Hence, the complete formula will become:
=SUBSTITUTE(TEXT(A2/B2,"####/####"),"/",":")
You can increase the # symbols in the above formula to increase the accuracy depending on the number of digits you have. When you are done, copy the formula to the remaining cells by using the Fill Handle.
In the above formulae, use the cell addresses correctly, otherwise, you will get an error.
Read: How to Calculate Simple Interest in Excel.
How do you enter a ratio value in Excel?
There is no direct way to show the result as a ratio in Excel. For this, you have to use appropriate formulae. You can also display the result as a fraction and then convert that fraction value into a ratio.
What is a ratio data in Excel?
The ratio data in Excel is the comparison of two values. For example, value 1 is how many times value 2. There are different ways to calculate and show ratios in Excel. You can use any of these methods.
That’s it. I hope this helps.
Read next: Calculate the Weight to Height ratio and BMI in Excel using this BMI calculation formula.
