How to Calculate Average of Averages in Excel (with Easy Steps)

In Excel, we use to solve different types of real-life problems. One of them is to Calculate 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 of excel. It is a straightforward process. In this article, I will describe how to Calculate Average of Averages in Excel with the necessary steps and illustrations.


Download Practice Workbook

Please download the practice workbook and practice yourself.


4 Suitable Steps to Calculate Average of Averages in Excel

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 Average of Averages in Excel with easy steps and illustrations. Follow the steps and increase your Excel skills.

Dataset of how to calculate average of averages in Excel


Step 1: Insert a New Column

  • First, Select the F.
  • Then Right-Click on the column.

Insert a column to how to calculate average of averages in Excel

  • After that, Click the Insert.

Insert a column to how to calculate average of averages in Excel

  • You will find a new column is added. Name it “Weighted Average”.

Insert a column to how to calculate average of averages in Excel

Read More: How to Calculate Average in Excel (Including All Criteria)


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)

Apply formula to how to calculate average of averages in Excel

  • You will find the result just like the picture given below.
  • Fill-handle the formula from F5 to F9.

Apply formula to how to calculate average of averages in Excel

  • As a result, you will find the Weighted Average for every student.

Apply formula to how to calculate average of averages in Excel

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)


Similar Readings


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

Change number format to calculate average of averages in Excel

  • Then, Change the number format to General.

  • Hence, You will find the result just like the picture shown below.

Read More: How to Calculate Average Percentage of Marks in Excel (Top 4 Methods)


Step 4: Final Result of Average

  • At last, you will find the result just like the picture given below.

Read More: How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)


Things to Remember

  • Use the absolute marks in the formula. Otherwise, an error will occur showing ‘#Div/0’.

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

Souptik Roy

Souptik Roy

Hello there. This is Souptik Roy. I graduated from Naval Architecture & Marine Engineering department. I am trying to explore the world of Microsoft Excel and want to increase my analytical power apart from my Engineering degree, as I have a curious mind. this is why I am trying to learn Excel Operations and write articles to help others.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo