MrExcel Publishing
Your One Stop for Excel Tips & Solutions

pivot tables

Posted by jim on November 03, 2001 12:48 AM

i need to calculate a weighted average from my cost data.

i am new to pivot tables, but it appears that pivot tables basically sum, count etc the variables. can the program handle this they problem?

Posted by Mark W. on November 03, 2001 8:25 AM

Okay, suppose that we want to calculate a weighted
grade average based on the following data list:


Of course, a weighted average is calculated by
dividing the sum of the products of the grades
and their weights by the sum of the weights;
however, a PivotTable won't allow you to
calculate the individual products needed for the
numerator of this ratio. Even with the use of a
Calculated Field all of the Grades and all of the
Weights will be summed prior to multiplication.
In order to circumvent this limitation you must
include the product of Grade and Weight in your
data list. Thus, your revised data list would


Now you can create a Calculated Field using the
Insert | Calculated Field... menu command. The
formula would be: ='Grade*Weight'/Weight.
Use this new field (preferable renamed as
"weighted average") in the DATA area of your

P.S.: The weighted average for this data set is