Hi all,
I'm trying to work out a moving cost price per below. My formula in column I works until the the 2 yellow highlighted cells where the cost price should be 50 (orange cell row 10) and 43.33 (orange cell row 11). The reason being it is summing all of the cells whereas it should only sum from the existing row as this is a new share batch (i.e. it should sum all of the purchases to the extent they have been fully sold, in which case it should not fix row 4 but rather fix row 10) - is that possible?
I'm trying to work out a moving cost price per below. My formula in column I works until the the 2 yellow highlighted cells where the cost price should be 50 (orange cell row 10) and 43.33 (orange cell row 11). The reason being it is summing all of the cells whereas it should only sum from the existing row as this is a new share batch (i.e. it should sum all of the purchases to the extent they have been fully sold, in which case it should not fix row 4 but rather fix row 10) - is that possible?
| stock purchase example.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 3 | Date | Bought/Sold | Number | Price | Amount (Base) | Cost Price | ||||
| 4 | 10-Jun-22 | B | 100 | 48.00 | (4,800.00) | 48.000 | ||||
| 5 | 13-Jun-22 | B | 100 | 46.00 | (4,600.00) | 47.000 | ||||
| 6 | 14-Jun-22 | B | 100 | 45.00 | (4,500.00) | 46.333 | ||||
| 7 | 14-Jul-22 | B | 50 | 43.00 | (2,150.00) | 45.857 | ||||
| 8 | 31-Jul-22 | S | (300) | 40.00 | 12,000.00 | 45.857 | ||||
| 9 | 05-Aug-22 | S | (50) | 35.00 | 1,750.00 | 45.857 | ||||
| 10 | 26-Aug-22 | B | 5 | 50.00 | (250.00) | 45.915 | 50.000 | |||
| 11 | 27-Sep-22 | B | 100 | 43.00 | (4,300.00) | 45.275 | 43.333 | |||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F4:F11 | F4 | =ROUND(((-C4 * D4) + E4), 2) |
| G4:G11 | G4 | =-SUMIFS(F$4:F4, B$4:B4, "=B") / SUMIFS(C$4:C4, B$4:B4, "=B") |
| H10 | H10 | =-F10 / C10 |
| H11 | H11 | =SUM(F10:F11)/ -SUM(C10:C11) |
