Waterfall logic (subtracting every row with conditions)

I have two tables (top i.e. Total level and bottom i.e. by Product and Tenure level). I need to breakdown the total level to product level by a waterfall logic.

The logic is that I need to follow the ordering so that the first row of adjustment will be -3400 (row1). And every subsequent row will need to deduct from the total amount.
-3500-(-3400)
-3500-row2-row1
.and so on…
However, the adjustments cannot be overly adjusted (validate columns cant be negative).

As shown, row2 column H is -29612. This is an over-adjustment. Hence, for row2, i shouldnt adjust and would need to take -1988 instead.
The next row would be the same until there’s no negative amount in the validate column anymore (see final output - columns M & N).

Finally i would need to perform this for > 10 customers and the product varies for each customer.

Hi @EmilyW,

in order for anyone to provide help, it would be beneficial if you could share the data so one must not typewrite it. It would also help better understanding your request.

Best
Mike

4 Likes