Cumulative SUM and IF

It's not clear to me how you are deriving the results, even though I've read your description. I think you might need to explain one series in more detail.

Thanks for reply.

Imagine that in order_qty column are standard orders which are placed once per week for one specific item. But, sometimes it can happen that we make special (preo_qty) orders, which can happen in any week. Related to order_qty, we are talking about forecasted values not the real ones. Also, preo_qty are scheduled for future. The purpose of this tool is to somehow exclude "double orders". If preo_qty exists, then we must "use" all quantity of preo_qty before returning to weekly orders (order_qty). NOTICE: do not be confused with repeated values in preo_qty. The first value of preo_qty counts only. This is only how ERP exports data. It would be harder to recode ERP then to make excel table for help.

For example 1.

Row = 4; order_qty is 5, and preo_qty is empty. In such case we always use order_qty value.
Row = 5; order_qty is 5, and preo_qty is empty. In such case we always use order_qty value.
Row = 6; order_qty is 0, and preo_qty is 20. Because 20 > 0, we are now starting to use this value, until is completely used per weekly orders. new_OQ = 20.
Row = 7; order_qty is 5, and preo_qty is used. 20 - 5 = 15. preo_qty is now 15 and it is not needed to place additional weekly order. new_OQ = 0.
Row = 8; order_qty is 0, and preo_qty is used. 20 - 5 = 15. preo_qty is now 15 and it is not needed to place additional weekly order. new_OQ = 0.
Row = 9; order_qty is 5, and preo_qty is used. 20 - 10 = 10. preo_qty is now 10 and it is not needed to place additional weekly order. new_OQ = 0.
Row = 10; order_qty is 5, and preo_qty is used. 20 - 15 = 5. preo_qty is now 5 and it is not needed to place additional weekly order. new_OQ = 0.
Row = 11; order_qty is 5, and preo_qty is used. 20 - 20 = 0. All preo_qty of 20 pieces is used. From down below we are starting to use normal order_qty, until new preo_qty pops put.
Row = 12; order_qty is 0, and preo_qty is used. In such case we always use order_qty value.
Row = 13; order_qty is 5, and preo_qty is used. In such case we always use order_qty value.
Row = 14; order_qty is 0, and preo_qty is used. In such case we always use order_qty value.
Row = 15; order_qty is 5, and preo_qty is used. In such case we always use order_qty value.
Row = 16; order_qty is 0, and preo_qty is used. In such case we always use order_qty value.
Row = 17; order_qty is 5, and preo_qty is used. In such case we always use order_qty value.
Row = 18; order_qty is 5, and preo_qty is used. In such case we always use order_qty value.


Sorry, it is hard to explain. I hope you will have motivation to help me until the end solution. And I will provide you necessary inputs.

Simply speaking, logic should consider order_qty until the preo_qty shows. If the preo_qty is greater then order_qty, then start using preo_qty until its value is completely used by cumulative order_qty (starting from row in which preor_qty firstly showed).

Milan B.

Originally Posted by LemitoSanchez

Did you had any progress with formula version of file?

Same question with josephteh in #8. Why D12 was expected to be 0?

Not sure how to start doing running SUM after some condition is fulfilled.

What is the condition?
It might also help if you would manually place the correct values in column H so that we can compare those to the output of our proposed formulas/code.
Let us know if you have any questions.

Is this it?

=IF(C4="","",C4-SUM(B$4:B4))