## Understanding Ratios:

- Ratios express the relative size between two or more numbers.
- The ratio is obtained by dividing one number (the antecedent) by another (the consequent).
- The general format of a ratio is “
**a:b**,” where “**a**” and “**b**” can be integers, decimals, or fractions.

## Dataset Example:

- Let’s use a dataset with two columns:
**Number 1**(in column B) and**Number 2**(in column C). - We want to calculate the ratio of
**Number 1**to**Number 2**and express it as a**percentage**.

### Method 1 – Using the GCD Function:

- The
**GCD function**finds the greatest common divisor between two numbers. - We’ll use this value to calculate the ratio and percentage.

**Step-by-Step Calculation:**

- In cell
**D5**, enter the following formula:

`=B5/GCD(B5,C5)&":"&C5/GCD(B5,C5)`

This formula calculates the ratio between cell **B5** and **C5** and displays it as **a****:b** in cell **D5**.

- Drag the
**Fill Handle**down to cell**D7**to fill the entire range**(D5:D7**) with ratios.

- In cell
**E5**, enter the formula:

`=B5/C5`

This calculates the quotient of **Number 1** divided by **Number 2**.

- Drag the
**Fill Handle**down to cell**E7**to fill the entire range (**E5:E7**) with**quotient values**.

- Select the range
**E5:E7**, go to the**Home**tab, and click the**%**(**Percentage**) sign in the**Number**group. This converts the**quotient**values to**percentages**.

**Result:**

- The range
**E5:E7**now shows the**percentage****value**of**Number 1**relative to**Number 2**.

**Breakdown of the Formula**

The **GCD(B5,C5)** function finds the greatest common divisor, and the expressions **B5/GCD(B5,C5)** and **C5/GCD(B5,C5)** give us the ratio values. The final formula **B5/GCD(B5,C5)&“:”&C5/GCD(B5,C5)** combines these values with the ratio sign “**:**”. You can use this method to calculate ratio percentages in Excel.

**Read More: **How to Convert Percentage to Ratio in Excel

### Method 2 – Combining SUBSTITUTE and TEXT Functions:

**Steps**

- In cell
**D5**, enter the following formula:

`=SUBSTITUTE(TEXT(B5/C5,"######/######"),"/",":")`

This formula calculates the **ratio** between cell **B5** and **C5** and formats it as **a:b** (replacing “**/**” with “**:**”).

- Drag the
**Fill Handle**down to cell**D7**to fill the entire range (**D5:D7**) with ratios.

- In cell
**E5**, enter the formula:

`=B5/C5`

This calculates the **quotient** of **Number 1** divided by **Number 2**.

- Drag the
**Fill Handle**down to cell**E7**to fill the entire range**(E5:E7**) with quotient values.

- Select the range
**E5:E7**, go to the**Home**tab, and click the “**%**” (Percentage) sign in the**Number**group.

- This converts the
**quotient values**to**percentages**.

**Breakdown of the Formula**

**TEXT(B5/C5,”######/######”):**This function will return the quotient of the division of the cell**B5**by**C5**and format it as a fraction.**SUBSTITUTE(TEXT(B5/C5,”######/######”),”/”,”:”):**This formula will substitute the “**/**” with the “**:**” in the fraction.

**Read More: **How to Convert Ratio to Decimal in Excel

### Method 3 – Applying Simple Division:

**Steps**

- In cell
**D5**, enter the formula:

`=(B5/C5)&":"&"1"`

This expresses the **ratio** of **Number 1** to **Number 2** with respect to **1**.

- Drag the
**Fill Handle**down to cell**D7**to fill the entire range (**D5:D7**) with ratios.

- In cell
**E5**, enter the formula:

`=B5/C5`

This calculates the **quotient** as before.

- Drag the
**Fill Handle**down to cell**E7**to fill the entire range (**E5:E7**) with quotient values. - Select the range
**D5:D7**, and click the**percentage**icon from the**Home**tab.

- Now the range
**E5:E7**shows the**percentages**of**Number 1**relative to**Number 2**.

**Read More: **How to Do Ratio Analysis in Excel Sheet Format

### Method 4 – Using Combined Formula:

**Steps**

- In cell
**D5**, enter the following formula:

`=LEFT(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))-1)&":"&MID(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))+1,3)`

This formula calculates the **ratio** and **formats** it as **a:b**.

- Drag the
**Fill Handle**down to cell**D7**to fill the entire range (**D5:D7**) with**ratios**.

- In cell
**E5**, enter the formula:

`=LEFT(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))-1)/MID(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))+1,3)`

This calculates the **percentages**.

- Drag the
**Fill Handle**down to cell**E7.** - Select the range
**E5:E7**and click the percentage icon.

- Now the range
**E5:E7**shows the**percentages**of**Number 1**relative to**Number 2**.

**Breakdown of the Formula**

**ROUND(B5/C5,5):**- This function calculates the quotient of the division of values in cells B5 and C5.
- It rounds the result to 5 decimal places.

**TEXT(ROUND(B5/C5,5),“###/###”):**- The
**TEXT**function formats the rounded quotient as a fraction. - The format “###/###” ensures that the fraction is displayed correctly.

- The
**SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”)):**- This formula finds the location of the “/” character within the formatted fraction.
- It starts searching from the left side of the text.

**LEFT(TEXT(ROUND(B5/C5,5),“###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”))-1):**- The
**LEFT**function extracts the portion of the text from the left side up to the specified location (just before the “/”). - This gives us the numerator of the ratio.

- The
**MID(TEXT(ROUND(B5/C5,5),“###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”))+1,3):**- The
**MID**function extracts a specified section of text from a particular position. - In this case, it extracts the three characters after the “/” (the denominator of the ratio).

- The
**LEFT(TEXT(ROUND(B5/C5,5),“###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”))-1)/MID(TEXT(ROUND(B5/C5,5),“###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”))+1,3):**- Finally, this function divides the extracted numerator by the extracted denominator.
- The result is the ratio expressed as “a:b.”

**Read More: **How to Graph Ratios in Excel

**Download Practice Workbook**

You can download the practice workbook from here:

## Related Articles

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

You have an extra 0 in your formula in section 1:

=B5/GCD(B5,C5)&”:”&C5/GCD(B5,C50)

it should read:

=B5/GCD(B5,C5)&”:”&C5/GCD(B5,C5)

Thanks a lot for providing the correction. Both the Image and the Formula has been updated.