How to add a weighted average in my pivot table?

Question

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).

score 15 · Accepted Answer · answered Jul 09 '13 at 16:28

15

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

answered Jul 09 '13 at 16:28

SeanC

15,695
5
45
66

Thank you for your help. However I don't really know what is a helper column. Is it an excel functionality for pivot table ? If not, it means that I need to calculate my helper column in my sql query (which is not a problem). My pivot table is based on an SQL query. – Leep Jul 10 '13 at 06:32
Just added a helper column in my sql query and then I've done what you said. It works like a charm ! Thank you for all ! – Leep Jul 10 '13 at 07:20

John Denniston · Answer 2 · 2015-03-17T10:58:23.553

Given you are after the Excel Pivot table version of a weighted average, I think you might find this article useful: http://excelribbon.tips.net/T007129_Weighted_Averages_in_a_PivotTable.html

The only thing it doesn't mention is what to do if your weighting sums to zero (in which case you will divide by zero). To avoid that ugliness you can use your Pivot table's DisplayErrorString and ErrorString properties, e.g.

oPivot.DisplayErrorString = True
oPivot.ErrorString = "--"

Though obviously that may hide real errors elsewhere in your Pivot table.

score 0 · Answer 3 · answered Jul 09 '13 at 14:10

0

Try to use

=SUMPRODUCT(A1:A6, B1:B6)/SUM(B1:B6)

This article may help you: Calculate Weighted Average in Excel by Ted French

answered Jul 09 '13 at 14:10

mzy

1,754
2
20
36

1

First thanks for trying to help me. I know how to calculate a wheighted average but I want to include it in my pivot table so this average is automatically generated according to the way my data are grouped. For example, the wheighted average of all data in 2011 or the wheighted of all the data where the name of the buyer is "toto". – Leep Jul 09 '13 at 14:14

How to add a weighted average in my pivot table?

3 Answers3

Linked