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

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.

Thanks for your help.
0
rgerl01
Asked:
rgerl01
1 Solution
 
Harry LeeCommented:
You can insert a Calculated Field to your Pivot Table.

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

1) Select your Pivot Table
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
 
Jeff GeiselmanCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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