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

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

• excel
• excel-formula
• cumulative-sum

Share Improve this question Follow asked Mar 25 at 20:20 ElizaBeso000ElizaBeso000 11566 bronze badges 2

• 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

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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:

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

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