Hi all,
I have a sheet shown below which has in Column B a list of numbers - this list can have up to 500 values in the column (I only listed 10 numbers in my simple example). Column A has the row # associated with each value. The sum / total I want to get is in cell E2. Its 100 but this is an input cell the value can be changed to put in any number. Cells F6 to F9 have the answers. The row length can change (e.g. F6 to F15) to show all the possible row combinations which give the sum total of 100.
I tried using a solver method but it only gives 1 possible answer. Am thinking its possible to use dynamic array formulas to do this in combination with the new Lambda function - since it accomodates recursion. Does anyone have a possible solution? I put image of problem as an attachment too....
I have a sheet shown below which has in Column B a list of numbers - this list can have up to 500 values in the column (I only listed 10 numbers in my simple example). Column A has the row # associated with each value. The sum / total I want to get is in cell E2. Its 100 but this is an input cell the value can be changed to put in any number. Cells F6 to F9 have the answers. The row length can change (e.g. F6 to F15) to show all the possible row combinations which give the sum total of 100.
I tried using a solver method but it only gives 1 possible answer. Am thinking its possible to use dynamic array formulas to do this in combination with the new Lambda function - since it accomodates recursion. Does anyone have a possible solution? I put image of problem as an attachment too....
Get subset adding up to a total from a list of #s.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Row # | Data | Total to get | ||||||||
2 | 1 | 20 | 100 | ||||||||
3 | 2 | 65 | |||||||||
4 | 3 | 72 | All combination of rows #s adding to to 100? | ||||||||
5 | 4 | 51 | Answer: | ||||||||
6 | 5 | 18 | 1,6 | ||||||||
7 | 6 | 80 | 2,9 | ||||||||
8 | 7 | 49 | 4,7 | ||||||||
9 | 8 | 13 | 7,10 | ||||||||
10 | 9 | 35 | |||||||||
11 | 10 | 51 | |||||||||
12 | |||||||||||
Sheet1 |