Hi, i'm trying to create some subtract formula, but no idea how should i do it, the table below would show the result i desire:
<tbody>
</tbody>
For ROW 5, as i sell the entire stock, i need the First 500 cost at USD1, the remaing 1,500 at USD2, so how can i create and present an automate formula in the excel table?
Thanks.
| A | B | C | D | E | F | ||
| 1 | DATE | STOCK ITEMS NAME | COST PER STOCK (IN USD) | QUANTITY OF STOCKS | CUMULATIVE/ BALANCE OF STOCKS | REMARK | |
| 2 | 1/1/2019 | XYZ | USD 1 | 1000 | 1000 | BUY | |
| 3 | 15/1/2019 | XYZ | USD 1 | (500) | 500 | SELL | |
| 4 | 31/1/2019 | XYZ | USD 2 | 1000 | 1500 | BUY | |
| 5 | 2/2/2019 | XYZ | USD ??? | (-1500) | 0 | SELL | |
<tbody>
</tbody>
For ROW 5, as i sell the entire stock, i need the First 500 cost at USD1, the remaing 1,500 at USD2, so how can i create and present an automate formula in the excel table?
Thanks.
