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")
=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 |