Using a Pivot Table to Calculate a Weighted Average
Posted by Steven Taylor on August 4, 2010
Lets assume that your data looks something like this:
- A B C D
3 Grades Cost Staff Weight
4 Grade A 10,000 5
5 Grade B 12,000 3
6 Grade C 14,000 2
In order to create a pivot table with a weighted average you will need to do the following:
- In cell D4 of the above you would enter the following formula: B4 * C4.
- Copy the formula you just created down to cell D6.
- From Excel’s menu bar, click on Data
- From the menu that drops down, click on PivotTable and PivotChart Report
- The Pivot Table wizard starts up
- Click the Next button at the bottom.
- Here you need to select the data for your pivot table. Using the data from above this would be equal to A3: D6. Once the range is selected, click next.
- Click layout on the bottom left
- Drop the Grades into the “rows” selection and Staff in the “data” section.
10. Click ok and then finish
11. Now you will have a pivot table but you still need to add the weighted average calculation to the table.
12. The weighted average calculation is added to the pivot table by using a calculated field.
13. To create a calculated field you need to open the pivot table toolbar. If the pivot table toolbar does not automatically appear when you select your pivot table you can access it from top Excel menu bar under View > Toolbars > Pivot Table.
14. Click the down arrow next to the word PivotTable at the left side of the PivotTable toolbar. Excel displays a menu.
15. Choose Formulas | Calculated Fields. Excel displays the Insert Calculated Field dialog box.
16. In the Name box, enter a name for your new field for example “Weighted Average”.
17. In the Formula box, enter the formula you want used for your weighted average, such as =Weight/Staff. Note you can use the field names from the field list at the bottom of the Insert Calculated Field dialog box.
18. Click OK.