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

Method 1 – Using the Basic Division Formula

STEPS:

• Select your output cell (E5 here).
• 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.
• 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.
• Drag the Fill Handle icon from your output cell to the whole desired output range (E5 to E12 here).
  

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

Read More: How to Calculate Average Ratio in Excel

Method 2 – Using the TEXT Function

STEPS:

• Select any blank cell.
• 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.
• Press Enter.
  

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

Read More: How to Calculate Male Female Ratio in Excel

Method 3 – Applying the ROUND Function

STEPS:

• Select any blank cell.
• 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.
• 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.
• To get ratios for all your input numbers, you can use the fill handle feature or copy the formula manually to all the other output cells.
  

Method 4 – Using the GCD Function

STEPS:

• Select your output cell.
• 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.
• Press Enter.
  
• 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 the Practice Workbook

Related Articles

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

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