Calculate Ratio in Excel Formula

The ratio usually compares the values. It helps us to tell how much one value is smaller or bigger than the other. In mathematics, a ratio is a relationship between two values showing how many times the first value contains the additional value.

For example, suppose in a dataset, we have the value 5 in cell A2 and 30 in cell B2, and we need to determine the ratio. In this scenario, we can use the RATIO formula in Excel. In this example, we can use the following formula in cell C2:

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

=1:6.

It helps to define the comparisons between two different values or things.

In Excel, there is no specific function to calculate the ratio. Instead, there are four functions to calculate the ratio in the Excel formula, which we can use per the requirements.

Following are the functions to calculate the ratio in the excel formula:

  • Simple Divide Method.GCD Function.SUBSTITUTE and TEXT Function.Using ROUND Function.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Ratio in Excel Formula (wallstreetmojo.com)

How to Calculate Ratio in Excel?

Let us learn how to calculate the ratio in the Excel formula with the help of a few examples of each function.

#1 – Simple Divide Function

The formula for the Simple Divide Method in ExcelDivide Method In ExcelDivide in Excel is used for division applications, where (/) is the symbol and we can write an expression =a/b, where a and b represent two numbers or values to be divided.read more:

= value1/ value2 &”:“&“1”

Example of Simple Divide Function

We have data where we have two values. We will have to calculate the ratio of the two numbers.

Steps to calculate the ratio in Excel formula with the help of the simple divide function are shown below:

  • Data with two values, as shown below:

  • Now, select the cell in which you want the ratio of the given values, as shown below.

  • Now, use simple divide function to calculate the ratio as =first value/second value&” : “ & “1”.

  • Press the “Enter” key to see the result after using the simple divide function.

  • Now, drag the function to the below cells to calculate the ratio of complete data and find the result as shown below.

#2 – GCD Function

A formula for GCD Function:

= value1 / GCD (value1, value2) &”:“& value2 / GCD (value1, value2)

Example of GCD Function

We have data where we have two values. We will have to calculate the ratio of two numbers in Excel.

Steps to calculate the ratio in Excel formula with the help of the GCD function are shown below:

  • First, calculate the GCD to make it less complicated than GCD (first value, second value).

  • Now, use the GCD function method to calculate the ratio as =first value/ GCD (first value, second value) & ” : “ & second value/ GCD (first value, second value).

  • Press the “Enter” key to see the result after using the GCD function.

  • Now, drag both the functions to the below cells to calculate the ratio of complete data and find the result as shown below.

#3 – SUBSTITUTE and TEXT Function

A formula using the SUBSTITUTE Function in excelSUBSTITUTE Function In ExcelSubstitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.read more and Text Function in excelText Function In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more.

= SUBSTITUTE (TEXT (value1/value2, “#####/#####”),”/”, “:”)

Example of SUBSTITUTE and Text Function

Steps to calculate the ratio with the help of the SUBSTITUTE and TEXT functions are shown below:

  • Now, select the cell in which you want the ratio of the given values, as shown below.Now, use the SUBSTITUTE and TEXT function to calculate the ratio as =SUBSTITUTE(TEXT(first value/second value, “#####/#####”),”/”, “:”).

  • Press the “Enter” key to see the result after using the SUBSTITUTE and TEXT functions.

#4 – ROUND Function

A formula for Round Function.

= ROUND (value1/value2, 1) &“:”& 1

Example of Round Function

Steps to calculate the ratio of two numbers with the help of the ROUND function are shown below:

  • Now, use the ROUND function to calculate the ratio as =ROUND( first value/second value,1)&“:”&1

  • Press the “Enter” key to see the result after using the ROUND function.

Things to Remember while Calculating Ratio in Excel Formula

  • There should be numeric values for which you want to calculate the ratio in Excel.Both the values should be positive, and the second should not be zero.There is no specific function to calculate the ratio; as per the requirement, we can use the functions.

This article is a guide to Calculate Ratio in Excel formula. Here, we discuss calculating ratios in Excel using 1) Simple Divide function 2) GCD function 3) SUBSTITUTE and TEXT functions, and 4) ROUND function with examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

  • Calculation of Percent Error FormulaCalculation Of Percent Error FormulaPercentage error formula is calculated as the difference between the estimated number and the actual number in comparison to the actual number and is expressed as a percentage, to put it in other words, it is simply the difference between what is the real number and the assumed number in a percentage format.read moreMultiply in ExcelMultiply In ExcelMultiplication in excel is performed by entering the comparison operator “equal to” (=), followed by the first number, the “asterisk” (*), and the second number.read moreSubtraction Formula in ExcelSubtraction Formula In ExcelThe subtraction formula in excel facilitates the subtraction of numbers, cells, percentages, dates, matrices, times, and so on. It begins with the comparison operator “equal to” (=) followed by the first number, the minus sign, and the second number. For example, to subtract 2 and 5 from 15, apply the formula “=15-2-5.” It returns 8.read more“Not Equal to” in Excel