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

Answer 1

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

Answer 2

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.

Answer 3

Try to use

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

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