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.

**Table of Contents**hide

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

Here, **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**