Create daily running total that subtracts from previous days when necessary

I have a file that contains a row of values for production and another row for orders.
I need a third row that will subtract orders from production, creating a list of available inventory. The available inventory row should be a running total, by day, that subtracts from current and previous days inventory when necessary.

Please help with the formula I would need to put in the available column that only subtracts from previous days when necessary, but still shows negative if the orders column is greater than the running total of the production column.

Example:
Capture.JPG

In C6 have =C4-C5
In D6 have =C6+D4-D5. This formula can then be copied across row 6 to the other columns.

Originally Posted by gak67

In C6 have =C4-C5
In D6 have =C6+D4-D5. This formula can then be copied across row 6 to the other columns.

The problem with that is it doesn't adjust the available inventory for previous days to reflect that it is no longer available. I need some sort of if statement that will adjust C6 and D6 to also reflect 0 available because the inventory that was previously available for Monday and Tuesday has now been sold (on Wednesday).

Capture.JPG

Originally Posted by alijo

The problem with that is it doesn't adjust the available inventory for previous days to reflect that it is no longer available. I need some sort of if statement that will adjust C6 and D6 to also reflect 0 available because the inventory that was previously available for Monday and Tuesday has now been sold (on Wednesday).

Attachment 468188

If you use my formulas, or those proposed by the others below mine, in C6 you will have 100 (100 production less 0 orders). In D6 you will have 200 (100 from C6 plus 100 production less 0 orders). In E6 you will have 0 (200 from D6 plus 100 production less 300 orders). There is no need for any if statements.

Originally Posted by gak67

If you use my formulas, or those proposed by the others below mine, in C6 you will have 100 (100 production less 0 orders). In D6 you will have 200 (100 from C6 plus 100 production less 0 orders). In E6 you will have 0 (200 from D6 plus 100 production less 300 orders). There is no need for any if statements.

Yes, I understand the formulas proposed and how they work, but that is where I'm running in to a problem. Those formulas don't adjust the Monday and Tuesday totals to reflect that there is no longer availability for those days because of the large order placed on Wednesday.

As soon as the orders on Wednesday are updated to a value of 300, there will no longer be 100 available on Monday or 200 available on Tuesday. There will be 0 available for Monday, 0 available for Tuesday, and 0 available for Wednesday because the order for 300 pieces wiped out all of the previously available inventory.
I would like the total in C6, D6, and E6 to reflect that zero value. I need a running total that adjusts not only forward, but backward as well as the orders come in... Hopefully this makes sense.

I am looking for a formula that gives the following result in row 6:
Capture.JPG

Originally Posted by Phuocam

Add IF function ...

=IF(SUM($C$5:C5),SUM($C$4:C4)-SUM($C$5:C5),0)

Now is there a way to alter that formula to still give daily totals if all of that day's inventory has not been used up? And maybe keep the daily totals within their own columns instead of making a running total each day?

For example:
Capture.JPG

Originally Posted by Phuocam

Add IF function ...

=IF(SUM($C$5:C5),SUM($C$4:C4)-SUM($C$5:C5),0)

The problem with that one is if all of my orders are 0, then my availability is also showing 0 instead of the actual values...

Capture.JPG