Calculation on Last Inputs into a List

Please see my attachment for an example. I am trying to obtain a formula that will automatically update itself as new data is added to the list (i.e. a self updating range for a sum formula)

Column A contains purchased lbs of a product. Colum B contains the cost of the purchase. Column C contains the average cost of the purchase. As I make additional purchases, the data will be added to this list.

Let's say I currently have 400 Lbs in inventory (all other purchases identified in the list have been sold). How can a formula be written to sum a row range of the last purchases in the list so that the sum of the range equals the current inventory. That is, per the attached example, the formula would have to sum rows 8 and 9 of column A and consider the partial inventory of row 7 (e.g. 400 lbs of current inventory minus the sum of rows 8 and 9). If the current inventory was 500 lbs, the formula would have to sum rows 9, 8, 7, and the partial inventory of row 6.

Part two of the problem, is that how many ever rows are required to be summed in column A, the same is done for column B with the exception of the partial row. To consider the effect of the partial row, the formula would take the current inventory (in lbs) less the sum of the full rows (e.g. rows 8 and 9 if the current inventory is 400 - for example purposes), multiply it by the average cost of the partial row, and add it to the sum of rows 8 and 9.

These two formulas in columns A and B would allow me to obtain the average cost of my current inventory.

Can this be done with the use of the offset formula?

Thanks.

I know it's little complicated, but I couldn't do better than this:

Example(1).xls

You'll see bunch of #VALUE error in example - that's because I put number (1000 in I column purposely. Remove it and you'll get OK. That's becase 1000 is greater than max number of quantity (wich show you that you can't use greater number than max).

Also, I made few changes. For workbooks where you will continuosly add new values in it it's better to put SUM at the TOP of the table, so you can easily add new values (no need for inserting row every time), and also calculations are easier.

Then I need to reverse table E column so I could look on it step by step does it exceed desired input.

I belive it can be easier, but I don't see it right now.

Hope it will be helpfull anyway.

One more thing.. If you enter 400 in current inventory you'll get desired result ($2.022,88).

However, once when you enter new number THIS VALUE WILL CHANGE. You'll need to enter 400 once again to get wanted result or delete all number up to 400.