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.

How to Calculate Average Ratio in Excel


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.

How to Calculate Average Ratio in Excel

  • Press ENTER.

How to Calculate Average Ratio in Excel

  • 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.

How to Calculate Average Ratio in Excel

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.

How to Calculate Average Ratio in Excel

  • 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.

How to Calculate Average Ratio in Excel

  • 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.

How to Calculate Average Ratio in Excel

  • 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.

How to Calculate Average Ratio in Excel

  • 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.

How to Calculate Average Ratio in Excel

  • 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.

How to Calculate Average Ratio in Excel


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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo