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.

How to Calculate Weighted Average in Excel Pivot Table: with Easy Steps

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

The weighted average is considered as the average where weight is determined for each quantity required to average. This type of average calculation helps us determine the relative importance of each amount on average. A weighted average can be considered more accurate than any general average as all the numbers in the data set are assigned to the same weight.

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.

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

Now, you will get the below result. Then, use the Fill Handle (+) tool to copy the formula to the rest of the 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.

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

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

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

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.

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.

Download the Practice Workbook

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

Weighted Average Pivot Table.xlsx

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.

<< Go Back to Calculated Field in Pivot Table | Pivot Table Calculations | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!