CODEWITHSUNDEEP
excelpivot-table
Leep
Leep

Reputation: 457

How to add a weighted average in my pivot table?

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

Upvotes: 15

Views: 57324

Answers (3)

John Denniston
John Denniston

Reputation: 167

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.

Upvotes: 2

SeanC
SeanC

Reputation: 15923

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

Upvotes: 16

mzy
mzy

Reputation: 1764

Try to use 

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

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

Upvotes: 0

Related Questions