How to get average cost of remaining (unsold) stock?

Here is my excel sheet having buying details on the left and the selling details on the right.
A2 is the total buying average.
I2 is the total selling average.
E2 is the remaining Stock.
E4 is the Cell where I need to get the desired result of average cost of remaining (unsold) stock.

I have tried to explain it while using the data of rows 6 and 7. The average buying cost of remaining (unsold) stock.

=((C6*(B6-H6)) + (C7*(B7-H7)))/((B6-H6)+(B7-H7))

Is there any formula or shortcut method to get the desired result for whole of the sheet data, i.e. average cost of all remaining (unsold) stock.

Excel Forum.png

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new, I shall provide it fir you this time.)

https://chandoo.org/forum/threads/ho...d-stock.44215/

and

https://www.mrexcel.com/board/thread...stock.1132985/

Answer given by chandoo.org/forum/members/peter-bartholomew.45701/ on Chandoo Excel forum.
This post is closed now.

If you wish to Check the answer and see the Excel attached please check on chandoo.org/forum/threads/how-to-get-average-cost-of-remaining-unsold-stock.44215/#post-263798

= SUM( (1-sell.quantity/buy.Quantity)*buy.amount ) / SUM(buy.Quantity-sell.quantity)
would be closer to the mark. The first sum is over the proportion remaining multiplied by the purchase cost and the second sum is the remaining stock.

Here sell.quantity, buy.quantity, buy.amount are named range.

If you are using anything but the latest 365 version of Excel the formula will need to be committed with CSE as an array formula. The alternative is to define a named formula e.g. averageCost to hold the formula. Any formula that is not on the grid is and always was evaluated as an array calculation.