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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 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)

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


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


<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo