to to calculated the weighted average if the inventory above zero

[joudgate]

i need a help
i have an excel sheet for portfolio management all orders in the same sheet (buy and sell) , i need to calculate if the historical record for purchasing ticker ticker is not zero will calculate a weighted average , if i sell an ticker will deduct from the inventory , and present the exist inventory with its value in another sheet

ticker	b/s	quantity	price	comm	total
1111	b	5	10	1	51
1111	b	5	15	1	76
1111	s	5	13	1	64

ticker	q	avg cost	p/f
1111	5	12.7	0.5

 

[dermie_72]

average = sum / quantity

=sumifs(Price,Ticker column, Ticker number, Quantity column, Quantity number)/countifs(Ticker column, Ticker number, Quantity column, quantity number)