Need remaining average inventory price after partial inventory is sold

Hi guys,

I try my best to explain what I might be doing.

I'm buying inventory everyday and sometimes a couple of times a day, and each time, the price is different so I'm using excel to calculate the average price of all the inventories I had bought and had used this formula:

=SUMPRODUCT(B2:B2000,C2:C2000)/SUM(B2:B2000)

It works well until when I sold partial of my stock at a higher price and the formula took into account the "profit" and average cost value becomes negative.

Will it be possible to set a rule so that each time column B has a negative value (which means I sold the item) the remaining inventories will be equally divided among the previous positive value of column B and multiply by the respective cost of purchase to find the new average? Or will it be possible to find the previous highest unit cost with respect to the previous positive value of inventories and mutiply by it? Or whatever better idea for solving this issue?

I have attached the excel in here too.
Thanks in advance and any help would be greatly appreciated.

Hi Ben! I complicated myself too much. Sorry if the question sound dumb! But That's exactly what Im Looking for!!! Many Thanks!