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:

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:

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

• excel
• excel-formula
• cumulative-sum

• This is the knapsack problem. Formulas are probably not the right approach here. – BigBen Commented Mar 25 at 20:21
• Do you have any suggestions for a different approach in excel? My dataset is small enough that I can do this manually but I'd like something I can automate for future datasets. – ElizaBeso000 Commented Mar 25 at 20:24

2 Answers 2

=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:

=IF(SUM($A$2:A2) <= 100, SUM($A$2:A2),
IF(LARGE(IF(ISNUMBER($D$1:D1),$D$1:D1),1)+A2<=100,
LARGE(IF(ISNUMBER($D$1:D1),$D$1:D1),1)+A2,"Over"))

With legacy Excel such as Excel 2013 you can apply this formula. The formula must be entered with ctrl+shift+enter as an arrayformula.