excelexcel-formulacumulative-sum

Cumulative budget that skips entries that exceed budget


I have a column of item costs listed top-down in order of priority that I need to purchase with $100. I would like to be able to maximize my $100 while maintaining my priority purchases as much as possible. I have created a cumulative cost column that states "over" whenever I am over the $100 budget, but I have later entries that still fit within that budget. How can I "skip" the over budget entries in my cumulative column?

Here is an example dataset:

Item Cost Cumulative Cost (Current Output) Desired Output
40 40 40
40 80 80
40 Over Over
20 Over 100
20 Over Over

And here is the formula I am using for the cumulative cost (current output): IF(SUM($A$1:A1) <= 100, SUM($A$1:A1), "Over")


Solution

  • =DROP(REDUCE(0,A1:A5,LAMBDA(a,b,LET(c,MAX(a)+b,VSTACK(a,IF(c<=100,c,"over"))))),1)

    This takes the cumulative sum from too to bottom if each added value stays under the maximum.

    So for the following data results in shown result, but will not look if the maximum can be met else way as pictured in the most right column:

    Data formula result not formula result
    50 50 50
    30 80 skip
    25 over 75
    25 over 100
    10 90 over