CODEWITHSUNDEEP
excelpivot-table
Alexander Abramovich
Alexander Abramovich

Reputation: 11438

How to calculate a pivoted weighted average in Excel?

The data I have is:

Category Name | Item name | Param 1 | Param 2
---------------------------------------------
Category A    | Item 1    | 10      | 20
Category A    | Item 2    | 15      | 25
Category B    | Item 3    | 20      | 30
Category B    | Item 4    | 40      | 35

How can I calculate a pivoted weighted average per items in each Category?

Category A | (10*20+15*25)/(20+25)
Category B | (20*30+40*35)/(30+35)

Upvotes: 1

Views: 6669

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34180

Insert a helper column after Param 2 headed P1 X P2:-

=C2*D2

Highlight the table and insert a pivot table.

Select Category as row label and sum of Param 2 and sum of P1 X P2 as sigma values.

Now go to Fields, items and sets on the ribbon and create a calculated field called Weighted Average equal to 'P1 X P2'/'Param 2'.

enter image description here

Upvotes: 1

eshwar
eshwar

Reputation: 694

Assuming you have category in Col A, this array formula will do

Category A { =SUMPRODUCT (IF(A2:A4=A2,C2:C5,""),IF(A2:A4=A2,D2:D5,""))/SUM(IF(A2:A4=A5,C2:C5,"")),}

Just in case you are not aware, for array formula you don't enter the curly braces but enter Ctrl - Shift - Enter and Excel does it

Upvotes: 0

Andreas
Andreas

Reputation: 23958

This should do it for A I believe...
Not tested

=vlookup("Category A", A2:A$5,3)*vlookup("Category A", A2:A$5, 4)+vlookup("Category A", A3:A$5,3)*vlookup("Category A", A3:A$5, 4)//Sumif(D2:D5,"Category A")

Edit: did not notice that you wanted to multiply. Edited with a formula that may work. Not tested and not sure about it.

Upvotes: 0

Related Questions