How to calculate weighted average in an Excel Pivot Table?
In general, it’s easy to calculate the weighted average by combination of SUMPRODUCT and SUM functions in Excel. However, the calculated fields seem not support the functions in a pivot table. Therefore, how could you calculate the weighted average in a pivot table? This article will introduce a solution.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Supposing you have created a pivot table as below screenshot shown. And I will take the pivot table as example to calculate the weighted average price of each fruit in the pivot table.
1. First of all, add a helper column of Amount in the source data.
Insert a blank column in the source data, type Amount as column name, next type =D2*E2 in the first cell of this helper column, and finally drag the AutoFill Handle to fill the whole column. See screenshot:
2. Select any cell in the pivot table to activate the PivotTable Tools, and then click Analyze (or Options) > Refresh. See screenshot:
3. Go ahead to click Analyze > Fields, Items, & Sets > Calculated Field. See screenshot:
4. In the Insert Calculated Field dialog box, please type Weight Average in the Name box, type =Amount/Weight (please change the formula based on your field names) in the Formula box, and then click the OK button. See screenshot:
Now you return to the pivot table, and you will get the weighted average price of each fruit in the subtotal rows. See screenshot: