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

Get FREE Advanced Excel Exercises with Solutions!

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


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.

How to Calculate Average Ratio in Excel

  • Next, press ENTER.

  • Now, use Fill Handle to AutoFill the rests.

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

  • Finally hit ENTER to have the Ratio.

How to Calculate Average Ratio in Excel

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.

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, the 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

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

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


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.

How to Calculate Average Ratio in Excel

  • Now, press ENTER to have the Average Ratio.


Similar Readings


Practice Section

For more expertise, you can practice here.

How to Calculate Average Ratio in Excel


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

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo