Weighted Average Price

[carl]

I have this data:

Buy/Sell Price Qauntity

Buy 10 25
Buy 15 10
Sell 5 5

I am trying to calculate my "position" (Buy's minus
Sell's) and average price:

Position Average Price
+ 20 ??

Can Excel Do this for me ?

Thank you in advance

 

[NH]

assuming col A contains Sell or Buy, col B contains Quantity and Col c
contains Price

The closing position of Quantity =
SUMIF(A1:A3,"Buy",B1:B3)-SUMIF(A1:A3,"Sell",B1:B3)
Average Price

=(SUMPRODUCT(--(A1:A3="Buy")*(B1:B3)*(C1:C3))-SUMPRODUCT(--(A1:A3="Sell"),--
(B1:B3),--(C1:C3))/SUMIF(A1:A3,"Buy",B1:B3)-SUMIF(A1:A3,"Sell",B1:B3)

 

[carl]

hi. when i put this formula in it told me that the
parentheses were misplaced/missing. i tried the excel
suggestion but did not get the correct average price.

am i doing something wrong ?

 

[Domenic]

Assuming that your data starts in the second row...

Average Price:

=(SUMPRODUCT((A2:A4="Buy")*(B2:B4)*(C2:C4))-SUMPRODUCT((A2:A4="Sell")*(B2
:B4)*(C2:C4)))/(SUMIF(A2:A4,"Buy",C2:C4)-SUMIF(A2:A4,"Sell",C2:C4))

Hope this helps!