Intriguing problem, and boy,did I try to get this into a single-celled formula! But I came up with something that may work.
| A | B | C | D | E | F | G | H | I | J | K | L |
---|
Stock | Qty | Price/Share | Date | Total Amt | Cost per share (FIFO) | Helper1 | Helper2 | Row | Avg Price/share | | | |
zzz | | | | | | | | | | | | |
zzz | | | | | | | | | | | | |
zzz | | | | | | | | | | | | |
zzz | | | | | | | | | | | | |
zzz | | | | | | | | | | | | |
zzz | | | | | | | | | | | | |
zzz | | | | | | | | | | | | |
yyy | | | | | | | | | | | | |
zzz | | | | | | | | | | | | |
yyy | | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1.210526[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]2-Jan[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"]-75[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]3-Jan[/TD]
[TD="align: right"]-97.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]4-Jan[/TD]
[TD="align: right"]-14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]5-Jan[/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]7[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]6-Jan[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]8[/TD]
[TD="align: right"]-95[/TD]
[TD="align: right"]1.6[/TD]
[TD="align: right"]7-Jan[/TD]
[TD="align: right"]-152[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]9[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1.7[/TD]
[TD="align: right"]8-Jan[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]10[/TD]
[TD="align: right"]-25[/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]9-Jan[/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]11[/TD]
[TD="align: right"]-75[/TD]
[TD="align: right"]0.15[/TD]
[TD="align: right"]10-Jan[/TD]
[TD="align: right"]-11.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L2[/TH]
Put the formula in H2, copy down to the end of your data, or to as far as you think you'll get. Put in the I2 formula and copy down. Finally put in the L2 formula. These formulas will calculate the average price per share, which you can then copy to the F column.
In this example, we want to calculate the average price/share for the transaction in row 8. Enter 8 in K2. Now the values in the H column show you how many shares are available to sell. H2 shows 0, we bought 100, but sold them all in rows 4-6. H3 shows 85, since 15 were sold in row 6. Row 7 shows 50 since none of those shares have been sold yet. Now looking at column I, we find out how many we need to take from each available row to make up the 95 we're selling. 0 from row 2, since those were all sold. 85 from row 3, and 10 from row 7. Now we know how many from row 3 and row 7 we need, and the prices on those rows, so now we just create the weighted average with the formula in L2. At this point, you'd copy the value in L2 to F8.
Whew! Tricky, complicated, and maybe VBA would be better. But this does seem to work. After you set it up, you only have 2 steps, put the row in K2, then copy the value to column F. Good luck!