In Excel, we use to solve different types of real-life problems. One of them is to Calculate the Average of Averages. In real life, an Average of averages or weighted average is needed in preparing results of a school or in paying wages to laborers concerning working hours, etc. However, you can prepare the reports containing weighted averages with a generic formula in Excel. It is a straightforward process. In this article, I will describe how to Calculate the Average of Averages in Excel with the necessary steps and illustrations.
How to Calculate Average of Averages in Excel: 4 Suitable Steps
Before jumping into the steps, let’s introduce our dataset first. Here, we consider a dataset of the Final Result of ABC School. The dataset has 4 columns B, C, D, & E indicating Roll, Results of Test 1, Test 2 & the Final Test. The dataset ranges from B4 to E9. Besides this table, we can see another table containing marks weightage in Test 1, Test 2, & Final Test respectively. Now, I will discuss how to Calculate the Average of Averages in Excel with easy steps and illustrations. Follow the steps and increase your Excel skills.
Step 1: Insert a New Column
- First, Select the F.
- Then Right-Click on the column.
- After that, Click the Insert.
- You will find a new column is added. Name it “Weighted Average”.
Read More: How to Average a Column in Excel
Step 2: Apply SUMPRODUCT Formula to Calculate Weighted Average
- Now, Write down the following formula in the F5.
=SUMPRODUCT($H$6:$J$6,C5:E5)/SUM($H$6:$J$6)
- You will find the result just like the picture given below.
- Fill-handle the formula from F5 to F9.
- As a result, you will find the Weighted Average for every student.
Step 3: Change Number Format to General
- Go to the Home tab in your Toolbar.
- Then, Select the Number
- After that, you will find the number format is in ‘Percentage’.
- Then, Change the number format to General.
- Hence, You will find the result just like the picture shown below.
Read More: How to Average Negative and Positive Numbers in Excel
Step 4: Final Result of Average
- At last, you will find the result just like the picture given below.
Things to Remember
- Use the absolute marks in the formula. Otherwise, an error will occur showing ‘#Div/0’.
Download Practice Workbook
Please download the practice workbook and practice yourself.
Conclusion
In this article, I have tried to show how to Calculate Average of Averages in Excel. In addition, I have shown 4 handy steps to Calculate the Average of Averages. This method is very useful for both professionals and students or learners. I hope you have understood all the steps. If you have any queries, let me know in the comment sections.
Related Articles
- Average Attendance Formula in Excel
- How to Calculate Average, Minimum And Maximum in Excel
- How to Calculate Average True Range in Excel
- How to Calculate Average Percentage in Excel
- How to Calculate Average Percentage of Marks in Excel
- How to Calculate Class Average in Excel
- How to Calculate Average Revenue in Excel
- How to Calculate Average Quarterly Revenue in Excel
- How to Calculate Average Share Price in Excel
- How to Calculate Average Length of Stay in Excel
- How to Calculate Average Price in Excel
<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel