Pivot table and weighted average

[shell910]

I am trying to get a weighted average from a pivot table and I am trying to use insert calculated field. Currently I am getting the pivot tables and copying those in a regular spreadsheet to do the weighted average calculations. Very time consuming!!

00000000000-000014 9,300 253,692
00000000000-000023 8,500 380,265
Total 633,957

This is information out of of my pivot table and I am currently taking (253692/633957)*9300 + (380,625/633957)*8500 to get my weighted average. How can I do this in a pivot table?

Any thing that can be done to help would be greatly appreciated.

 

[seanoj]

Dredging up an old post as my first is probably not a great way to start on this board, but I noticed many unanswered threads about this subject...

I too have been wondering if it is possible to get a weighted average in a pivot table using just the raw data. As it is right now, I add columns to the raw data that multiply the value times the weight for every measure for which I am trying to calculate the weighted average. This is often many columns and adds a lot of, what I feel, is redundant data that takes up space and complicates the adding of rows to a table of raw data.

Is there a way to replicate the sumproduct() function in an excel pivot table? I have tried many Sum(weight * value)/sum(weight) type calculated measures, but these don't seem to work properly.

Any suggestions?

-Sean

 

[Dr. Demento]

All I can give is my perspective, which is that there are better tools than Excel to manage these types of issues: R, SAS, SPSS; even Mathmatica or MATLAB are better suited, but that's just my professional bias talking :-/

 

[Aladin Akyurek]

Or you might want to try Power Pivot...

 

[seanoj]

Trying to distribute the results to a diverse group of people, all of whom have excel and very few who have access to any of the named tools. Power Pivot is how I do it for myself, but we found that the Power Pivot sheets made in Excel 2013 are not really backwards compatible (can't refresh in Excel 2010) and doing them in Excel 2010 does not work either (crashes our Excel 2013 machines).

I am going to stick with the added columns for now. Thanks for your suggestions Dr. D and Aladin.