How to Calculate Ratio Between Two Numbers in Excel (5 Ways)

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:

how to calculate ratio between 2 numbers

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.

how to calculate ratio between 2 numbers

 =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.

how to calculate ratio between 2 numbers

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)

how to calculate ratio between 2 numbers

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


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)&”:”&1

Here, C5 and D5 refer to the Scores in the Religion of Peter and Jane respectively.

how to calculate ratio between 2 numbers

⧭ 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

Towhid

Towhid

Name: MD SHAJRATUL ALAM TOWHID Date of Birth: 6th September 1996 Religion: Islam Nationality: Bangladeshi

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo