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

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.

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.

Practice Section

For more expertise, you can practice here.

Download Practice Workbook

Average Ratio Calculation.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!