Calculate rollover based on conditions

[Ronal_bal]

Hi,

I'm trying to calculate the actual CUM based on conditions present in the status column.

Perform subtraction whenever status is Okay or Ahead between Qty and cum+in_transit.

The Catch is i need to perform a rollver subtraction from previous Actual CUM for the above status.

For status is delayed, i require to assume the previous Actual CUM.

for the next status if it is okay or ahead. add the qty from previous delayed to current qty and then subtract from the previous delayed actual cum.

I tried using multi-row formula but not getting there, any help would be appreciated.

Attached the excel file with expected output

[binuacs]

@Ronal_bal Can check the attached workflow works for you? The last value i got 120 instead of 108

[Ronal_bal]

@binuacs The logic you sent works 50% as expected. But if the first status is delayed then it it still calcualting cum_received - ASN Qty.

which should not be. And the as you said the last status is delayed, it is not taking the cummulative sum to previous actual Cum.

Thanks and appreciate for the help

[Ronal_bal]

@caltang @Qiu

[binuacs · Accepted solution]

@Ronal_bal updated the formula

[caltang]

@Ronal_bal Based on your requirements, @binuacs has solved it.

If it helps, do you mind marking his posts as accepted solutions to close your thread? Thanks