We calculate average in order to have a linear connection between the variables. Sometimes, we face the necessity of calculating the average of the **ratios** to have a certain pattern. In this article, we are going to discuss **how to calculate Average Ratio in Excel**.

For more simplification, we are going to use a **Dataset **containing **Products**, **Sales in January**, and **Sales in February **columns.

## Download Practice Workbook

## 2 Simple Ways to Calculate Average Ratio in Excel

### 1. Using GCD Function to Calculate Average Ratio

There is an amazing function named **GCD Function **which can be helpful while calculating** Average Ratio**. We can employ this function with the **AVERAGE** **Function** to find the value we want. The **GCD Function **returns the greatest common divisor for several integer values. As we know, the greatest common divisor is the largest integer value which divides both the numbers without a remainder.

**Steps**:

- Create two additional columns named
**GCD**and**Ratio**with the assumed dataset.

- Input the following formula to find out the
**GCD**in a cell (**e. E5**).

`=GCD(C5,D5)`

Where, **GCD Function **is applied between the values in cells **C5 **and **D5 **to find out the greatest common divisor.

- Press
**ENTER**.

- Now, use
**Fill Handle**to**AutoFill**the rests.

- Now, apply the following formula to find out the
**Ratio**:

`=C5/E5 & ":"& D5/E5`

Where, values in cells **C5 **and **D5 **are divided with the greatest common divisor and presented them as a ratio, to do so Iâ€™ve concatenated the values and used a **colon **(:) symbol.

- Hit
**ENTER**to have the**Ratio**.

You can see that our result in a conventional ratio format.

**AutoFill**the rests.

Weâ€™ve found the ratio, unfortunately, we canâ€™t calculate the average from here directly. To calculate the average you need to follow the following process.

- First of all, use the following the formula to have
**Average Sales in January**:

`=AVERAGE(C5:C9)`

Where, **Average Function **is applied in the cells **C5:C9**.

- Next, press
**ENTER**to have the**Average Sales in January**.

- Similarly,use the same formula to find the
**Average Sales in February**:

`=AVERAGE(D5:D9)`

Here, **Average Function **is applied in the cells **D5:D9**.

- Now, press
**ENTER**.

- Now, find the
**Greatest Common Divisor**between**Average Sales in January**and**Average Sales in February**using the following equation:

`=GCD(D11,D12)`

Where, the **Greatest Common Divisor **is calculated between **Average Sales in January **and **Average Sales in February**.

- Hit
**ENTER**.

- Now, use the formula given below to find the
**Average Ratio**:

`=D11/D13 & ":"& D12/D13`

Where, values in cell **D11 **and **D12 **is divided with the greatest common divisor and presented them as the **Average Ratio**.

** **

- Finally, press
**ENTER**to finish the process.

You may wonder why we didnâ€™t apply the **AVERAGE** function to the calculated **Ratio** column, once we have concatenated the values it becomes a text, so mathematical formulas will not be applied for that.

**Read More: ****How to Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)**

### 2. Applying Combined Formula of ISNUMBER, IF and AVERAGE Functions

We can use a combined formula of **ISNUMBER**, **IF **and **AVERAGE **to **calculate Average Ratio**. **The ISNUMBER Function **checks if the cell contains any number or not. **The IF Function **verifies if the value in the cell satisfies the condition or not. **The AVERAGE Function **returns the average of the arguments.

**Steps**:

- Select a cell to input the formula (
**e. C11**). - Input the following formula to find the
**Average Ratio**:

`=AVERAGE(IF(ISNUMBER(C5:C9/D5:D9),C5:C9/D5:D9,""))`

Where, I verified the number with **ISNUMBER Function** in **C5:C9 **& **D5:D9 **cells. Then, the **IF Function **checks whether it satisfies the condition or not. Then, the **AVERAGE Function **is applied to calculate the **Average Ratio**.

- Now, press
**ENTER**to have the**Average Ratio**.

**Read More: ****How to Calculate Ratio Between Two Numbers in Excel (5 Ways)**

## Practice Section

For more expertise, you can practice here.

## Conclusion

We have tried to explain **how to calculate Average Ratio in Excel **in 2 simple ways. We hope it will help the Excel Users. For further queries, comment below.

## Related Articles

**How to Graph Ratios in Excel (2 Quick Methods)****Calculate Compa Ratio in Excel (3 Suitable Examples)****Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)****How to Calculate Sharpe Ratio in Excel (2 Common Cases)****Debt Service Coverage Ratio Formula in Excel****Calculate Sortino Ratio in Excel (2 Methods)****How to Convert Percentage to Ratio in Excel (4 Easy Ways)**