Shajratul Alam Towhid
In Mathematics, the ratio is used to compare two values . And it helps to compare the relation between two numbers. Excel plays a very easy and handy role in calculating ratios even though the numbers are big and not divisible. In this article, we’ll see 5 ways how to calculate the ratio between two numbers in Excel.
Download Practice Workbook
5 Ways to Calculate Ratio Between Two Numbers in Excel
We have made a typical dataset of the Score of Peter and Jane based on different Subjects. The dataset is given below:
Now, we shall see the procedures and formulas to calculate the ratio of different subjects’ Scores for Peter and Jane.
1. Calculating Ratio Using the Simple Division Method
In the beginning, we can use the simple divide method when two values are divisible. Among them, one can be larger than the other, or both of the numbers can be equal. In Row 5 of the dataset, the Score of Peter and Jane in Physics are 80 and 40 respectively. Here, the larger value is 80 and the smaller value is 40. 80 is 2 times larger than 40 which means 80 is divisible by 40. So, we can easily use the following method to calculate the ratio.
=C5/D5&”:”&”1”Here, C5 and D5 cells refer to the Score of Peter and Score of Jane respectively.
⧭ Formula Explanation:
In this formula, we have divided 80 by 40 which gives 2 in return. So now we have 2 instead of 80 dividing by 40. And on the other side, we have used 1 instead of 40.
Read More: How to Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)
2. GCD Function to Calculate Ratio Between Two Numbers in Excel
For using this method, we first have to understand the GCD function to find the GCD. In the picture, the GCD of two numbers in Row 5 i.e the Scores of Peter and Jane in Biology are 70 and 58 respectively. So, we can find the GCD by using the formula:
=GCD(C5/D5)Here, C5 is the starting cell of the Subject.
Now, the formula for finding the ratio using the GCD function for the F5 cell is as follows.
=C5/GCD(C5/D5)&”:”&D5/GCD(C5,D5)
NOTE: The GCD function only works with integers.
⧭ Formula Explanation:
The formula appears to be difficult, but it is actually fairly simple. This is how it works:
=(formula for number 1)&”:”&=(formula for number 2)
The GCD function is used to find the greatest common divisor (GCD) of the two numbers on the left. The GCD is then used to split the first integer. The identical operations are performed with the second number on the right.
- After inputting the values, l the formula will be like the following:
=70/(GCD(70,58))&”:”&58/GCD(70,58)
- Next, we’ll get 2 as the GCD of 70 & 58. This output i.e. 2 will be divided by 70 and 58 like this:
=70/2&”:”&58/2
- Subsequently, we will get the calculated values which are 35 and 58.
=35&”:”&29
- Finally, the output will be as follows-
=35:29
Read More: How to Convert Percentage to Ratio in Excel (4 Easy Ways)
Similar Readings
- How to Calculate Sortino Ratio in Excel (2 Methods)
- Calculate Odds Ratio in Excel
- How to Calculate Male Female Ratio in Excel (3 Suitable Methods)
- Graph Ratios in Excel (2 Quick Methods)
3. Utilizing CONCATENATE Function
This formula also needs a GCD function. Rather it works in the same way as only using the GCD function. Here, we can add the CONCATENATE function as an extra to make the formula stronger. We can write the formula to find the ratio of the Scores Peter and Jane for Physics like this.
=CONCATENATE((C5/GCD(C5,D5),”:”,((D5/GCD(C5,D5))))
⧭ Formula Explanation:
This function first works only using the GCD function which we have explained in the previous method. Finally, the right and left operations are joined together using the CONCATENATE function with the colon (”:”) as a separator.
Read More: How to Calculate Ratio Percentage in Excel (4 Easy Methods)
4. Applying SUBSTITUTE and TEXT Functions
It is the combination of two effective functions. This method works like a charm just like the GCD function. Here we have the below values to calculate the ratio. The formula for the two values in the E5 cell to find the Ratio is.
=SUBSTITUTE(TEXT(C5/D5,”#####/#####”),”/”,”:”)
5. Employing ROUND Function to Calculate Ratio Between Two Numbers in Excel
The ROUND function is a very effective function to calculate a ratio when we want to find out rations with decimals for accurate comparison.
Here, we will deal with values that are not divisible and will find out the ratio by directly dividing the larger value by the smaller value. This will give the output converting the smaller value as 1. We will simply make an output of ration in decimal form. We can write the formula to find the ratio of the Scores of Peter and Jane in Religion like this.
=ROUND(C5/D5,1)&”:”&1Here, C5 and D5 refer to the Scores in the Religion of Peter and Jane respectively.
⧭ Formula Explanation:
We can split this formula into two different parts to understand it.
- First of all, we have to use the round function to divide the larger value by the small value and get the result with one decimal.
- Secondly, we have to use a colon and 1 in the end.
Things to Remember
- Accurate calculation of GCD is mandatory to calculate the ratio while using CONCATENATE function and GCD
- The simple divide method is not friendly for indivisible numbers.
- If we have simple values to calculate a ratio, it’s not good to use the SUBSTITUTE and the TEXT
Conclusion
When the numbers are divisible, the ratio is calculated by using the division method in excel, but when the numbers are not divisible, the ratio can be calculated by using the GCD function or SUBSTITUTE and TEXT function or ROUND function. And these are the effective ways to calculate the ratio between two numbers in Excel. Anyway, don’t forget to share your thoughts in the comments section.
Related Articles
- How to Calculate Current Ratio in Excel (2 Suitable Examples)
- Calculate Compa Ratio in Excel (3 Suitable Examples)
- How to Calculate Average Ratio in Excel (2 Simple Ways)
- Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)
- How to Calculate Sharpe Ratio in Excel (2 Common Cases)
- Debt Service Coverage Ratio Formula in Excel
