Ratio in Excel

The Ratio is defined as a method to compare two data sets that enable the users to conclude whether a given data is greater or lesser. It also provides the proportion between 2 factors or numbers. Therefore, people mostly use Ratio when they want any two data sets.

How to Calculate Ratio in Excel?

With Excel, calculating the ratio of two numbers is very simple and easy. However, Excel doesn’t provide any predefined function that can help us calculate a ratio. But still, you can easily calculate it by creating customized formulas.

In this tutorial, we will discover the various ways to calculate ratios in excel and see some examples.

Key Points

Before moving forward, let’s look at some key points that one should know before calculating the Ratio in Excel.

1. The methods mentioned below will only work and give you the desired output if the two specified values are numeric.
2. The specified values should be positive, and the second value shouldn’t be zero; otherwise, the result would not be defined.
3. Make sure to use the concatenation operator to avoid errors properly.
4. Instead of the CONCATENATION operator, you can also use the CONCATENATION formula.

Method #1 Calculate Ratio by using Divide method

Moving forward, let’s understand the step-by-step logic behind calculating the Ratio of two numbers.

1. Suppose you have two parameters, X and Y, where X is assigned a value of 20 and Y is assigned a value of 10.
   
2. Start the formula with the “=” sign (Equal) to go to the edit mode of that cell. To calculate the ratio, we will have two different syntaxes. In the first half of the syntax, divide cell A2 with B2 (A2/B2), and in the second phase, divide cell B2 with B2 itself and separate both the syntax using a colon (:). At last, we will be combining the result of both the syntax using concatenate operator (&).
   Formula =( A2/B2) & “:” & (B2/B2)
   
3. Bring your mouse cursor to cell C2 and type the above formula, and hit the enter button on your keyboard.
   
4. As you can see in the above image, it will have your output. The Ratio of 20 and 10 comes out to be 2:1. However, you can format the value to make the output look more attractive and readable.

Method #2 Calculate Ratio by using Divide with one method

This method is just an advanced version of the above method. If you closely look at the formula in the above example, you will notice it is split into two syntaxes, and in the second syntax, we have divided cell B2 with B2 itself.

By dividing the same parameter with itself it will always give the output as 1. Therefore in the second syntax, instead of mentioning B2/B2, we can replace it by 1 as it’s the default value. Therefore it will simplify the formula and will be a lot easier to use. Let’s look at the step-by-step derivation and implementation of this formula with the help of an example.

1. We will again take two parameters, X and Y, where X is assigned a value of 20 and Y is assigned a value of 10.
   
2. To calculate the ratio, go to cell C2 and start typing the Ratio formula.
3. Initiate the formula with the “=” sign. This method will again have two syntaxes and in the first half of the syntax divide A2/B2 and the second half just mention 1. Separate both the syntax using a colon (“:”) and join the output of the syntax using the concatenation operator (&). So the concluded formula will be as follows:
   Formula =( A2/B2) & “:” & 1
   
4. Once you have typed the formula, hit the enter button to fetch the output. The Ratio of 20 and 10 comes out to be 2:1.
   

NOTE: Whatever method you use, you will notice the Ratio output will be the same for all.

Method #3 Calculate Ratio by using GCD Function

The advantage of Excel is that you can solve a problem using various approaches. Therefore one of the approaches to find the ratio of two numbers is through GCD (Greatest Common Divisor) Function. Though many users’ GCD is a little complicated simultaneously, many users use this function because of its short syntax.

Greatest Common Divisor is the largest number that completely divides the number and leaves the comparative ratio an output. In excel, we have a predefined formula to calculate the GDP of two numbers. Therefore GCP automatically finds the greatest number to divide it by the value present in the numerator.

Syntax of GCD Formula

Let’s see the step-step implementation of the GCD and calculate the Ratio of two numbers.

1. We will take the same parameters that we have taken in method 1 and method 2.
   
2. Go to cell C2 and initiate the formula by typing the “=” sign. Again we will split the formula into two syntaxes, and in each syntax, we will turn by turn divide both the numbers with GCD. As discussed above, to calculate GCD, we must select both cells A2 and B2 separated by a commae., GCD (A2, B2).
3. In the first half of the syntax, divide the cell A2 with GCD (A2/ GCD (A2, B2)), and in the second half, divide the cell B2 with GCD (B2/ GCD (A2, B2)). Separate both the syntax using a colon (:) and combine the output with the help of concatenation operator (&).
   Formula = A2 / GCD (A2,B2) & “:” & B2 / GCD (A2, B2)
   
4. Once you have typed the formula, hit the Enter button. Excel will immediately display the Ratio of the two numbers.
   

The benefit of using the GCD Ratio calculation method in excel is that it provides the output, which also appears good regarding number, character, and spaces.

NOTE: If you want to calculate the ratio of 3 or more values, the GCD method can is the best fit.

Method #4 Calculate Ratio by using ROUND Function

Another method of calculating Ratio in Excel is by using the ROUND function. It is an inbuilt Excel formula used to round any number to the specified number of digits.

Syntax for Round:

The advantage of using this function is that the user can calculate the ratio of two numbers with defined output containing precise decimals values and make an accurate comparison. Though the Round Function comes with a disadvantage as well that it is not applicable in all conditions.

Below given are the steps to calculate the Ratio using the ROUND Function.

1. Suppose you have two parameters, X and Y, where X is assigned a value of 10 and Y is assigned a value of 20. As you can see, X is smaller; therefore, this time higher number is not divisible with the smaller number.
   
2. In the C2 cell, initiate the formula by typing the “=” sign and call the inbuilt Round formula. It will ask for two parameters. In the first parameter, we will divide both the cell references (divide X with Y or A2/B2), and in the second parameter, we will pass 1 (as we have to round it to digit 1 only).
3. For the Ratio in the second phase, we will use 1 in the end. To separate the result of Round and 1, we will use colon (:) and later concatenate both the results using the concatenation operator (&). So the concluded formula will be as follows:
   Formula =ROUND(A2/B2,1)&”:”&1
   
4. Once you have typed the formula, hit the Enter button. Excel will immediately display the ratio of the two numbers in a proper format. The ratio of 10 and 20 comes out to be 5:1.