How to Calculate Ratio of 3 Numbers in Excel (4 Effective Ways)

Ratio is a mathematical measurement to compare between two or more values. This measurement is greatly helpful for data analysis, financial analysis, comparative analysis, forecasting, planning, efficiency, and accuracy. This measurement can also summarize, interpret, and visualize large data sets.

In this Excel tutorial, you will learn how to calculate ratio of 3 numbers in Excel. Though Excel doesn’t have any built-in feature or function that can be used to calculate the ratio of 3 numbers, you can use some customized formulas to do this. Here, you will see such 4 effective ways to calculate ratio of 3 numbers in Excel.

This overview image shows 3 random input numbers and all the effective formulas to find the ratio between those 3 numbers. The inputs are in Number 1, Number 2, and Number 3 columns and the final results are in the Ratio column.

4 Ways to Calculate Ratio of 3 Numbers in Excel

To calculate the ratio of 3 numbers, you can follow 4 effective ways: Using a basic Division formula, Using the TEXT function, Applying the ROUND function, and Using the GCD function.

For the first three methods, you will have to assume the first ratio as 1 and you will get the ratios of other numbers depending on the first number. However, you will get independent ratios of given random numbers if you use the GCD function approach.

Let’s explore the ways and formulas one by one:

Using Basic Division Formula

You can find the ratio of 3 numbers using a basic arithmetic formula. You have to use the basic division formula here.

To calculate ratio of 3 numbers in Excel using the basic division formula, follow the steps below:

1. Select your output cell (E5 here).
2. Insert the formula: ="1:"&C5/B5&":"&D5/B5
   In the formula, replace cell references B5, C5, and D5 with your input Number 1, 2, and 3.
3. Press Enter. You will find the ratio of the following 3 numbers each one separated by colon.
   Here, C5/B5 calculates the ratio of numbers 1 and 2. D5/B5 calculates the ratio of numbers 1 and 3.
4. Drag the Fill Handle icon from your output cell to the whole desired output range (E5 to E12 here).
   

You will get the ratio of 3 numbers for all your inputs in the Ratio column. However, this approach may display up to 15 decimal places for the fraction values.

Read More: How to Calculate Average Ratio in Excel

Using TEXT Function

The basic division formula returned fraction ratio numbers with long trails of decimal places. To trim those trailing decimal places and to keep the ratio in a shorter format, you can use the TEXT function as this function can round off the fraction ratio number to 2 or any other required decimal places.

Follow the steps below to calculate ratio of 3 numbers using the TEXT function:

1. Select any blank cell.
2. Insert the formula: ="1:"&TEXT(C5/B5,"0.00")&":"&TEXT(D5/B5,"0.00")
   Replace B5, C5, and D5 cell references with your Number 1, 2, and 3.
3. Press Enter.
   

You will get your desired ratio of 3 numbers with the same formula for all inputs in the Ratio column where every ratio has a maximum of two decimal places as per the formula.

Read More: How to Calculate Male Female Ratio in Excel

Applying ROUND Function

To find the ratio between 3 numbers and display the result in desired shorter formats, Excel’s ROUND function is another great function to use. Because, with this function, you can trim the result to whichever decimal places you want.

Follow the steps below to apply the ROUND function to calculate the ratio of 3 numbers in Excel:

1. Select any blank cell.
2. Insert the formula: ="1:"&ROUND(C5/B5,2)&":"&ROUND(D5/B5,2)
   Replace cell references B5, C5, and D5 with your input Number 1, 2, and 3.
3. Press Enter. You will get your desired output ratio of the following three numbers.
   Here, as the ROUND function’s num_digits argument is given as 2, so you will get a maximum of two decimals in your ratio results.
4. You can use the fill handle feature or copy the formula manually to all the other output cells to get ratios for all your input numbers.
   

Using GCD Function

To calculate the ratio of 3 numbers in terms of integer numbers, you can use the GCD function. In the previous methods, you have calculated the ratio of 3 numbers by dividing each number by the first number. And, the first ratio was assumed as 1 for every case. But, in this approach, you will have to calculate the ratio of 3 numbers by dividing each number by the Greatest Common Divisor (GCD) of the other two numbers and you will get ratios depending on each number involved.

Follow the steps below to use the GCD function to calculate the ratio of 3 numbers in Excel:

1. Select your output cell.
2. Insert the formula: =B5/GCD(B5,C5)&":"&C5/GCD(B5,C5)&":"&D5/GCD(C5,D5)
   Replace B5, C5, and D5 cell references with your input Number 1, 2, and 3.
3. Press Enter.
   
4. You can apply the fill handle feature or copy the same formula manually for all the other cells.
   As a result, you will get the ratio of 3 numbers in the Ratio column.
   

Read More: How to Calculate Sortino Ratio in Excel

Download Practice Workbook

Conclusion

So, in this tutorial, 4 effective ways were shown to calculate the ratio of 3 numbers in Excel. You can use a basic division formula, TEXT function, or ROUND function to get the ratios of three numbers assuming the first result is 1. And, to get fully independent ratios of 3 numbers, you will have to use the GCD function. Please don’t hesitate to ask any questions in the comment section below if you have any queries or confusion regarding this topic.

Related Articles

• How to Calculate Sharpe Ratio in Excel
• How to Calculate Odds Ratio in Excel
• How to Calculate Compa Ratio in Excel
• How to Calculate Current Ratio in Excel

<< Go Back to Ratio in Excel | Calculate in Excel | Learn Excel