Pivot table get weighted average with sumproduct

[bullit_nl]

Hi,

I want to use the sumproduct formula to calculate weighted averages in my pivot table.

Prices are in column B and quantities are in column M.

The problem is that the range of columns B and M are changing every time i refresh the data form the pivot table and the total at the bottom of my pivot table witch i need to see.

How can i use the sumproduct formula without having to edit it all the time and not include the total? I know that it's possible to use a dynamic range but don't know how to use it.

Formula:
=SUMPRODUCT(M6:M14;B6:B14)/SUM(M6:M14)

Thanks

 

[bullit_nl]

The quantity in the pivot table is only summed and the end. The quantity field fom the selected item contains data from many different dates and the same item also has different prices.

 

[bullit_nl]

-

 

[bullit_nl]

--

 

[Andrew Poulsom]

In what way is your pivot table different from this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Description	Quantity	Price	Value		Row Labels	Sum of Quantity	Sum of Price	Sum of Value	Sum of Field1
2	Product1	1	10	10		Product1	6	24	80	13.33
3	Product2	2	11	22		Product2	8	26	112	14.00
4	Product3	3	12	36		Product3	10	28	148	14.80
5	Product4	4	13	52		Product4	12	30	188	15.67
6	Product1	5	14	70		Grand Total	36	108	528	14.67
7	Product2	6	15	90
8	Product3	7	16	112
9	Product4	8	17	136
Sheet1 (2)

 

[bullit_nl]

there is one more column with different dates. My dates are in the column labels and i filter on product

 

[Andrew Poulsom]

Like this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Description	Date	Quantity	Price	Value	Ave Price			Column Labels
2	Product1	Aug-2014	1	10	10	13.33			Aug-2014			Sep-2014
3	Product2	Aug-2014	2	11	22	14		Row Labels	Sum of Quantity	Sum of Value	Average of Ave Price	Sum of Quantity	Sum of Value	Average of Ave Price
4	Product3	Aug-2014	3	12	36	14.8		Product1	1	10	13.33	5	70	13.33
5	Product4	Aug-2014	4	13	52	15.67		Product2	2	22	14	6	90	14
6	Product1	Sep-2014	5	14	70	13.33		Product3	3	36	14.8	7	112	14.8
7	Product2	Sep-2014	6	15	90	14		Product4	4	52	15.67	8	136	15.67
8	Product3	Sep-2014	7	16	112	14.8		Grand Total	10	120	14.45	26	408	14.45
9	Product4	Sep-2014	8	17	136	15.67
Sheet1

Cell Formulas
Range	Formula
F2	=ROUND(SUMIF(A:A,A2,E:E)/SUMIF(A:A,A2,C:C),2)

 

[Aladin Akyurek]

I want to sumproduct based on the description name in column A.

So if for example a discription name in A is "red bal" or "green fish" then i want to sum the quantities in column F and the prices in column H or R.

Let's fix the formula first and then make it dynamic.

=SUMPRODUCT(--($A$2:$A$100="red bal"),$F$2:$F$100,$H$2:$H$100)

Is this what you are after?

 

[bullit_nl]

Yes almost....

This gives me the total amount of the "red bal". But not yet the avarage price.

 

[Aladin Akyurek]

Yes almost....

This gives me the total amount of the "red bal". But not yet the avarage price.

Looks like

=SUMPRODUCT(--($A$2:$A$100="red bal"),$F$2:$F$100,$H$2:$H$100)/SUMIF($A$2:$A$100,"red bal",$F$2:$F$100)

Right?

 

[bullit_nl]

Yes! This is right...it gives me the average