Calculate Values based on Multiple Conditions

Hi All,

I have a slight problem with calculating end state values based on a time period of activity.

Basically, I wanting to calculate Receipts minus Issues in a cummulative manner but only when there is instances of activity. At the end, I want the formula to be able to reduce the On Hand quantity when the Receipts have ceased.

I have enclosed my required end state values in the attached sample file for reference.

Thanks heaps in advance.

Put this formula in B4 and copy across, it seems to calculate the same way you did in the red cells:

=MAX(SUM($B2:B2)-SUM($B$3:B3),0)

If you want the decimal places the same, you will have to adjust the number formatting for that row.


EDIT: I just duplicated your methodology, I wouldn't necessarily do it that way. Since this appears to work, too, this is probably what I would do:

B4: =B2-B3
C4: =MAX(B4+C2-C3,0) ...copied across to the end.