excel - Calculation of average cost -

excel - Calculation of average cost -

i have next table:

transaction_date ticker cost shares trade_cost ave_cost total_shares total_cost 12/1/2014 aaa 1.50 1,000 1,500.00 1.50 1,000 1,500.00 12/1/2014 bbb 10.00 250 2,500.00 10.00 250 2,500.00 12/10/2014 bbb 11.25 200 2,250.00 10.56 450 4,750.00 12/12/2014 aaa 1.75 800 1,400.00 1.61 1,800 2,900.00 12/15/2014 ccc 32.00 100 3,200.00 1.00 3,200 3,200.00

i have calculate ave cost, total shares, , total cost.

my formula total shares sec entry of bbb

=sumif(b2:b4,b2,d2:d4)

while formula total cost is

=sumif(b2:b4,b2,e2:e4)

next, sec entry of aaa on date 12/12/2014,

total shares = sumif(b2:b5,b5,d2:d5) total cost = sumif(b2:b5,b5,e2:e5)

dragging formula downwards require me manually alter range in sumif formula. how calculate running total shares , total cost @ every entry date without manually changing range in sumif formula?

i assuming info start cell a1 , first row has column names.

in cell i2 set function , re-create downwards =sumproduct(--($b$1:b2=b2),$d$1:d2) running total shares.

you work same way other running totals. running average totals set formula in cell i2: =sumproduct(--($b$1:b2=b2),$d$1:d2)/sumproduct(--($b$1:b2=b2)) , re-create down.

with sumif following: =sumif($b$1:b2,b2,$d$1:d2) , re-create down.

excel