How to add a weighted average in my pivot table?

excel pivot-table
Leep picture Leep · Jul 9, 2013 · Viewed 51.8k times · Source

I would like to know how to add a weighted average in my pivot table. In fact, I need to do the following calculation: SUM(col1 * col2)/SUM(col2).

I tried to do it by using the calculated field option but when I enter my formula, I just have the following result as an output: SUM((col1 * col2)/col2) which is equal to SUM(col1).

Answer

SeanC picture SeanC · Jul 9, 2013

You will need 1 calculated field, and 1 helper column

Helper Column

col3=col1*col2

Calculated field:

CF=Col3/Col1

If you try to do the helper column as a calculated field, it will sum col1 and col2, then multiply them together which results in a meaningless answer

Related questions

Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More
Filter Excel pivot table using VBA

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA. The code below doesn't work. Sub FilterPivotTable() Application.ScreenUpdating = False ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True ActiveSheet.PivotTables("…

Read More