• Status: Solved
• Priority: Medium
• Security: Public
• Views: 11115

# Pivot Tables - Calculated Fields - Weighted Averages

How does one create a weighted average in a pivot table?

The AVE function provides a simple average but most of my calculations and reports require weighted averages (Means).  Using a column outside and to the right of my Pivot Table is not a good work-around since the number of columns in my data often changes.  In the past, this has caused me to lose the data.

Appreciate an answer but please avoid using anything other than built-in formulas as I cannot program using a computer language.

0
rgerl01
1 Solution

Commented:
You can insert a Calculated Field to your Pivot Table.

Not sure which version of Excel are you using. In Excel 2010,

2) Keyboard Shortcut Alt-JT-J-F
3) Label the field Weighted Average
4) In the Formula field, do {Field 1} divided by {Field 2}.

Take a look at my sample. I have created a pivot table using Region, Department, Revenue, and Number of Employee.

My weighted average field is Revenue per Employee.

So in my formula field, I do =Revenue / # of Employees
Q-28284899.xlsx
0

Commented:
I found that I had to add a new column formula to the data (which I labeled 'SumProduct') with the formula  ='Revenue'   *   '# of Employee' in Harry's Q-28284899.xlsx example.

Then I inserted a Calculated Field into the PIvot Table that  was '= SumProduct / Revenue'.
Average-Weighted-2.xlsx
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.