Weighted Average

[gtd526]

Hello,
Looking for Weighted Average per Symbol (AK:AK).
Thank you.

The Whole Enchilada.xlsm
	AF	AG	AH	AI	AJ	AK	AL
1	Buys					Weighted Average
2	Current	QTY	Price	Cost		Symbol	Price
3	TECL	33	$57.58	$1,900.00		TECL	??
4	CYBR	25	$48.00	$1,200.00		CYBR	??
5	CYBR	20	$45.00	$900.00
6	TECL	25	$60.20	$1,504.90
Buy Worksheet

 

[etaf]

ignore , did not realise the cost - was how the price was calculated
use offthelip solution next

how about
=SUMPRODUCT(($A$3:$A$6=F3)*($B$3:$B$6)*($C$3:$C$6))/SUMIF($A$3:$A$6,F3,$B$3:$B$6)

Book6
	A	B	C	D	E	F	G
1	Buys					Weighted Average
2	Current	QTY	Price	Cost		Symbol	Price
3	TECL	33	57.57575758	1900		TECL	58.70517241
4	CYBR	25	48	1200		CYBR	46.66666667
5	CYBR	20	45	900
6	TECL	25	60.196	1504.9
Sheet4

Cell Formulas
Range	Formula
G3:G4	G3	=SUMPRODUCT(($A$3:$A$6=F3)*($B$3:$B$6)*($C$3:$C$6))/SUMIF($A$3:$A$6,F3,$B$3:$B$6)

 

[offthelip]

try this:

=SUMIF(A$3:A$6,F3,D$3:D$6)/SUMIF(A$3:A$6,F3,B$3:B$6)

 

[etaf]

use the solution from​

offthelip​

much better solution, i didnt see the relationship with the cost and price - i just assumed it was a selling price and so need to multiple that by quantity

sales * qty
for each row of that item
then work out the average

 

[gtd526]

try this:

=SUMIF(A$3:A$6,F3,D$3:D$6)/SUMIF(A$3:A$6,F3,B$3:B$6)

Perfect. Thank you.