We calculate the 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 the average ratio in** Excel**.

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

## How to Calculate Average Ratio in Excel: 2 Simple Ways

### 1. Using the 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**:

- First, create two additional columns named
**GCD**and**Ratio**with the assumed dataset.

- Then, input the following formula to find out the
**GCD**in a cell (**e.g. E5**).

`=GCD(C5,D5)`

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

- Next, 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 as a ratio, to do so Iâ€™ve concatenated the values and used a **colon **(:) symbol.

- Finally hit
**ENTER**to have the**Ratio**.

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

- Now
**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 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, the **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**.

- Then 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 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**

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

We can use a combined formula of **ISNUMBER**, **IF, **and **AVERAGE **to calculate the** 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**:

- First, select a cell to input the formula (
**e.g. C11**). - Then 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 the **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**.

**Similar Readings**

**How to Do Ratio Analysis in Excel Sheet Format****How to Convert Percentage to Ratio in Excel****How to Convert Ratio to Decimal in Excel**

## Practice Section

For more expertise, you can practice here.

**Download Practice Workbook**

## Conclusion

We have tried to explain how to calculate the 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 Calculate Male Female Ratio in Excel****How to Calculate Sortino Ratio in Excel****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****How to Calculate Ratio Percentage in Excel****How to Graph Ratios in Excel****How to Use Interest Coverage Ratio Formula in Excel****Debt Service Coverage Ratio Formula in Excel**