How to Quickly Calculate Ratio in Excel

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

Even in our daily life, we use this 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.

Get Better at Excel in 2018

A bundle of E-Books you need to thrive in Excel this year.

1. Calculate Ratio by using Simple Divide Method

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

calculate ratio in excel with simple dividing values

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

=A2/B2&”:”&”1″

calculate ratio in excel with simple dividing values enter formula

here's how this formula works...

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

understand how to use simple divide to calculate ratio in excel

PROs: #1. Simple to apply. #2. Easy to understand.

CONs: #1. Limited use. #2. Not applicable if the larger value is not divisible with the smaller value.

Pros:

  1. Simple to apply.
  2. Easy to understand.

Cons:

  1. Limited use.
  2. Not applicable if the larger value is not divisible with 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, GCD function is close enough.

It can help you to get common denomination for both of the values and then by using little concatenation, you can calculate the ratio.

calculate ratio in excel with gcd function values

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

=A2/GCD(A2,B2)&”:”&B2/GCD(A2,B2)

calculate ratio in excel with simple with gcd function values enter formula

here's how this formula works...

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

understand how to use gcd to calculate ratio in excel

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 SUBSTITUTE and TEXT function to calculate the ratio. This method works like a charm just like GCD function.

Here we have below values to calculate the ratio.

calculate ration in excel with text substitues values

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

=SUBSTITUTE(TEXT(A2/B2,”#####/#####”),”/”,”:”)

calculate ratio in excel with text substitute values formula

here's how this formula works...

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

understand how to use substitute text to calculate ratio in excel

First, you have to use 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 forward slash with a colon. And, we have substitute function for that.

PROs: #1. A dynamic formula, it doesn’t matter even if you change the values. #2. Simple to use and understand.

CONs: #1. If you have simple values to calculate a ratio, it’s not good to use this method.

4. Calculate Ratio with Round Function

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

Here we have values in which higher value is not divisible with 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.

calculate ratio in excel with round values

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

=ROUND(A2/B2,1)&”:”&1

calculate ratio in excel with round values formula

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 round function to divide larger value with the small value and get the result with one decimal. Second, you have to use a colon and “1” in the end.

PROs: #1. Useful when you want to get results with decimals. #2. Final value will more accurate.

CONs: #1. Not applicable in all the situations.

Sample File

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 which we have used above can help you to calculate ratio 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 to calculate 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.

Must Read Next

Content Protection by DMCA.com
2018-11-16T06:13:21+00:00

2 Comments

  1. Abhishekkumar Jasoriya 4 Sep, 17 at 1:41 pm - Reply

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

    • Puneet 9 Jul, 18 at 8:45 am - Reply

      You’re welcome. 🙂

Leave A Comment