Ratio In Excel Formula

Publication Date :

31 Mar, 2019

Blog Author :

Edited by :

Reviewed by :

Download FREE Ratio Formula In Excel Template and Follow Along!

Ratio Calculation Excel Template.xlsx

Table Of Contents

What Is 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.

Ratio In Excel Formula — *You are free to use this image on your website, templates, etc..* *Please provide us with an attribution link*

Key Takeaways

The ratios in Excel compare values to determine their relative size. In math, ratios show the relationship between two values, indicating how many times the first value contains the second.
When calculating the ratio of numeric values in Excel, one must ensure that both values are positive and the second value is not zero.
In Excel, there is no specific formula for calculating ratios. Users need to rely on other functions and formulas based on their requirements.
It is crucial to understand these functions and apply them to data sets accurately for meaningful results.

How To Calculate Ratio In Excel?

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
ROUND Function

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 Excel:

= 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:

Data with two values, as 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 excel and Text Function in excel.

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

Example of SUBSTITUTE and Text 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 with the help of the SUBSTITUTE and TEXT functions 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 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.

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

#4 - ROUND Function

A formula for Round Function.

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

Example of Round 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 of two numbers with the help of the ROUND 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 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.

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

Important Things To Note

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.