Calculating new average without factoring in prior irrelevant averages

UPDATE: I think I might have solved this on my own, but please help me check if i might have a mistake. I used the LARGE function. Please take a look at the updated excel file. Also, I think my new formula makes it a bit laggy when I was entering the data for the 'Trades' worksheet. Any suggestion to reduce the lag?





Hello guys,

thanks for the help for my previous post, it has been really helpful.
However, as I examined my Excel file, I found out a bigger flaw. I've tried to solve it but to no avail. I'm gonna need your help in this one. It has to do with average and the problem is detailed in the attached file.

The Problem is with calculating new average without factoring in prior averages that are irrelevant anymore. I need to calculate the average price that I bought my stocks at at any given time. This average keeps changing as I buy or sell stocks. At certain time, I have to disregard prior purchases/averages in the calculation of the new average price. How do I do this?

Thanks a bunch!

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

surfol, it would I think be a good idea to outline what happens in a more complex scenario, eg (same stock):

Buy 100 shares at 50
Buy 200 shares at 75
Sell 175 shares
Buy 50 shares at 90
Sell 150 shares
Buy 50 shares at 50

In essence it seems you wish to operate a FIFO system in your averages (perfectly understandable).

However, your examples don't cover the above scenario (the most complex) and though we can make [FIFO-based] assumptions as to what we think the Average prices should be for the above it would be best if you provide your expected results.
(Where stocks are involved people often have their own preferences)