Hello,

I have recently started trading which is irrelevant but i have created an excel spreadsheet to track my portfolio and that is very relevant to the problem.

The problem is as follows:

for simplicity let's say i bought 100 of 'A' at a total price of 1,000 and an average price of 10.

Then, i sell all 100 of A (the price at which i am selling does not matter because i am only trying to establish the average purchase price)

Then, i buy another 100 of A at a total price of 500 and an average cost of 5.

I calculate my average price by dividing total cost by total amount

The issue i have is that my spreadsheet returns an average price of 7.5 not 5.

The reason for this is because it does not recognise that i have sold out of my position so it gives me the total average price of all purchases no matter how many times i have sold. For instance, after the above example i would be left with 100 of A. But, my spreadsheet thinks i've got 200 because it doesn't know i have sold my original 100.

This problem is the final error i need to deal with in my spreadsheet (i hope). All other problems i have been able to resolve and i really don't want to have to buy a portfolio tracker when i have got this far in making my own!

I have attached a document that gives an example of how my 'trades' tab works on my spreadsheet.

Any advice would be much appreciated.

Honestly, at this point i am desperate for a solution. I have combed through youtube and google but cant seem to phrase my problem in a way that gets any results.

If i haven't explained my problem well enough i am more than happy to try explaining again.

This is my first time using this sort of forum for a solution so i apologies in advance if the way i have constructed this issue is problematic.