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