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

Written by Mrinmoy Roy

Reviewed by Tanjim Reza

Last updated: Jul 6, 2024

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.

Calculated ratio of 3 numbers using basic division formula

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.

Calculated ratio of 3 numbers with TEXT function

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.