Excel Guides

Weighted Averages in a PivotTable in Excel

A weighted average is an average that takes into account the importance, or weight, of each value. When you calculate a weighted average, you assign a weight to each value, then calculate the sum of the weighted values divided by the sum of the weights. This type of average is often used when you have data that represents a population with different levels of importance.

In a PivotTable, you can calculate a weighted average for numeric fields by adding a calculated field. A calculated field is a column that you add to a PivotTable that contains a formula. The formula can use the fields in the PivotTable as well as other mathematical operators.

To add a calculated field:

  1. Click anywhere in the PivotTable.
  2. On the Options tab, in the Calculations group, click Fields, Items, & Sets, and then click Calculated Field.
3. In the Name box, type a name for the field.
4. In the Formula box, enter a formula by using one or more existing fields.
For example, to calculate 10% above sales price for each product sold, enter this formula:[Sales Price]*1.1.< p align = "left" > 5 . To see how your formula works , click < b > Add to add the field to your PivotTable , then drag it next to the field on which it's based . You can also click < b > OK without adding the field to see how it would affect your results . If you're satisfied with your results , click < b > OK again . Otherwise , make changes to your formula and repeat steps 3 - 5 until you get the results you want .< p align = "left" >< o : p >& nbsp ;

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.