How to Calculate Weighted Average in Excel Pivot Table

In this article, I will discuss how to calculate weighted average in Excel Pivot Table. Finding weighted average in Pivot Table is a bit complex. Usually, in an Excel worksheet you can apply functions to find weighted averages. On the other hand, you cannot apply excel functions in a Pivot Table. So, in this case, we have to apply an alternative technique.


Download the Practice Workbook

You can download the practice workbook that we have used to prepare this article.


Easy Method to Find Weighted Average in Excel Pivot Table

Calculate Weighted Average in Excel Pivot Table by Adding an Extra Column (Helper Column)

The weighted average is a calculation that takes into account the varying degrees of importance of the numbers in a data set. In calculating a weighted average, each number in the data set is multiplied by a predetermined weight before the final calculation is made.

Basically, we calculate the weighted average in Excel using the combination of the SUMPRODUCT function along with the SUM function. However, in this method, we will use an alternative way as functions cannot be used in Pivot Table. So, we will add an extra column to the Pivot Table source data and thus calculate weighted averages.


Dataset Introduction

For example, we have a dataset containing different grocery items’ date-wise sales. Now, I will calculate the weighted average price for each of the grocery items in a Pivot Table.

Dataset Introduction

So, here are the steps associated with the process.


Step 1: Adding Extra Column

  • First, add an extra column (helper column), ‘Sales Amount’ in the above table. Next, type the below formula in the first cell of this new column.
=D5*E5

Adding Extra Column

  • Now, you will get the below result. Then, use the Fill Handle (+) tool to copy the formula to the rest of the column.

Adding Extra Column

  • As a result, you will get the following result.


Step 2: Creating Excel Pivot Table

  • Initially, click on a cell of the dataset (B4:F14) to create the Pivot Table.

Creating Excel Pivot Table

  • Next, go to Insert > Pivot Table > From Table/Range.

Creating Excel Pivot Table

  • Then, the ‘PivotTable from table or range’ window will show up. Now, if your ‘Table/Range’ field is correct, press OK.

Creating Excel Pivot Table

  • After that, the Pivot Table is created on a new sheet. Later, choose the PivotTable Fields as the below screenshot.

Creating Excel Pivot Table

  • As a consequence, you will get the following Pivot Table.


Step 3: Analyzing Weighted Average Excel Pivot Table

  • First, select on the Pivot Table.
  • After that, go to the Pivot Table Analyze > Field, Items, & Sets > Calculated Field.

Analyzing Weighted Average Excel Pivot Table

  • Subsequently, the Insert Calculated Field window will show up.
  • Now, type ‘Weighted Average’ on the Name field.
  • Then, we have divided the helper column by weight (Sales Amount/Weight) to get the weighted average.
  • Next, click on OK.

  • Finally, we got the weighted average price for each of the grocery items in the subtotal rows of our Pivot Table.


Conclusion

In the above article, I have tried to discuss the weighted average calculation method in Pivot Table elaborately. Besides, this method is very simple. Hopefully, the explanations will help you to find weighted averages in Pivot Tables. Please let me know if you have any queries regarding the article.

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo