**Contents**hide

A ratio is one of the common methods to compare two values with each other. And it can help you to compare variations between those two values.

Even in our daily life, we use the word RATIO to define the comparison between two different things. When it comes to Excel, we don’t have any specific single function which can help us to calculate a ratio.

But you can calculate it using some custom formulas. So today, in this post, I’d like to show you how to calculate ratios using 4 different ways.

Let’s get started, and make sure to download this sample file from here to follow along.

## 1. Calculate the Ratio by using the Simple Divide Method

We can use this method when the larger value is divisible by the smaller value. In the below example, we have values 10 and 2, where 10 is divisible by 2.

So you can use this method here. Insert the below formula into the cell and hit enter.

`=A2/B2&":"&"1"`

#### here’s how this formula works…

In this formula, we have divided 10 by 2 which gives you 5 in return. So now we have 5 instead of 10 by dividing by 2. And on the other side, we have used 1 instead of 2.

**PROs**: #1. Simple to apply. #2. Easy to understand.**CONs:**#1. Limited use. #2. Not applicable if the larger value is not divisible by the smaller value.

## 2. GCD Function to Calculate Ratio in Excel

As I said, there is no single function in Excel to calculate the ratio for you. But, the GCD function is close enough. It can help you to get a common denomination for both of the values and then by using a little concatenation, you can calculate the ratio.

And to calculate the ratio insert the below formula into the cell and hit enter.

`=A2/GCD(A2,B2)&":"&B2/GCD(A2,B2)`

#### here’s how this formula works…

Maybe it looks complex to you but it’s simple in reality. Let me explain this in two parts.

As I said, GCD can give you a common denominator for both of the values. So here you have to use GCD in both of the parts of the formula to get a common denominator.

After that, you have to divide both of the values with that common denominator. And, in the end, a little concatenation to join both of the values using a colon.

**PROs**: #1. It’s a dynamic formula, it doesn’t matter even if you change the values. #2 Easy to apply.**CONs:**#1. GCD function only works with integers and if you have decimals in your values, it will not work.

## 3. SUBSTITUTE and TEXT for Ratio Calculation

A combination of two awesome functions. Yes, you can use the SUBSTITUTE and TEXT functions to calculate the ratio. This method works like a charm just like the GCD function. Here we have the below values to calculate the ratio.

In a cell, insert the below formula and hit enter.

`=SUBSTITUTE(TEXT(A2/B2,"#####/#####"),"/",":")`

#### here’s how this formula works…

This formula also works in two different work parts TEXT and SUBSTITUTE.

First, you have to use the text function to divide both of the numbers and after that format the returning value in a fractional format.

Second, by dividing both of the values you have got 10, and when you convert this value into a fractional part you will get 10/1.

Third, replace the forward slash with a colon. And, we have a substitute function for that.

**PROs:**#1. A dynamic formula doesn’t matter even if you change the values. #2. Simple to use and understand.**CONs:**If you have simple values to calculate a ratio, it’s not good to use this method.

## 4. Calculate the Ratio with Round Function

Using the round function to calculate a ratio is also a useful method. This is especially useful when you want to calculate ratios with decimals for accurate comparison.

Here we have values in which the higher value is not divisible by the smaller value. So, in this situation instead of using them as they are you can divide them and show the final ratio with decimals.

Just insert the below formula into the cell and hit enter.

`=ROUND(A2/B2,1)&":"&1`

And, here you have a ratio with decimals.

#### here’s how this formula works…

You can split this formula into two different parts to understand it. **First** of all, you have to use the round function to divide the larger value by the small value and get the result with one decimal. **Second**, you have to use a colon and “1” at the end.

**PROs:**#1. Useful when you want to get results with decimals. #2. The final value will be more accurate.**CONs:**#1. Not applicable in all situations.

## Conclusion

As I said, a ratio is a useful method to compare two values with each other. And now, you have different methods to calculate it in your favorite application, yes, in Excel.

All the methods that we have used above can help you to calculate ratios in different situations with different types of values.

I hope this will help you in your work. Now, tell me one thing. Do you have any other method for calculating the ratio? Please share with me in the comment section. I would love to hear from you.

And please don’t forget to share this tip with your friends. I’m sure they will appreciate it.

You have a small bug on the image under #2 GCD Function

The image references A3 and B3, when it should be referencing A2 and B2. The formula in the screenshot is correct.

Overall great techniques! Thank you

Very useful, straightforward and simple information. I wasn’t left more confused. Thank you.

Thank you so much for sharing the ratio calculation.

How do I calculate a ratio when I only have one number?

I have been given Amounts contributed by 4 people and I need to right the contributions as a ratio on excel

None of these suggestions work me…

i want to get the formula for student teacher ratio

there is 370 students in a class

1 teacher is required for 40 students

2 teacher for 90 and

3 teacher for 120 and after it 1 teacher on each 40 students

When i entered formula in Cell C2 I get ” #NAME? ” this massage.

What to do?

“

I liked very much.

Good and useful

Thanks puneet .It’s such a useful platform to learn excel

You’re welcome. 🙂