How to Calculate Average Ratio in Excel (2 Simple Ways)

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.

Table of Contents hide

Download Practice Workbook

2 Simple Ways to Calculate Average Ratio in Excel

1. Using GCD Function to Calculate Average Ratio

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

Practice Section

Conclusion

Download Practice Workbook

Average Ratio Calculation.xlsx

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.

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.

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.