2
In other words, I'm looking for a sum range that consists of combinations of cells from an array. Given a set of numbers:
1 720 56 17 59 120 153 203 39 1 690 583 582 561 256 310 232 95 108 16 26 59 538 445 42 149
(call this A1:A26
)
The sum range I'm looking for is 1000-1500
. I'd like to be able to see which combinations of any cells from A1:A5
will have a sum within that range. Such as taking the sum of (A23, A24, A26
) or the sum of (A2, A11
).
The number of cells or combinations do not matter, as long as the sum falls within the given range. On top of that, I need to be able to identify the cells used in each combination.
I am grateful to anybody who can make my life any easier.
Thank you.
1Welcome to superuser. What have you tried so far to accomplish this? – CharlieRB – 2013-07-12T19:54:13.133
2
Oblig XKCD: http://xkcd.com/287/ (Not exactly the same scenario but close enough.)
– RLH – 2013-07-12T20:17:02.2271As I’m sure you realize, there are POWER(2,n) possible combinations (subsets) of a set with n members. I came up with a worksheet function solution, but it requires POWER(2,n) columns (or rows, if you transpose it). POWER(2,26) is 67108864. I’m not going to post my answer, because, even if Excel can handle that many columns (or rows), it’s not practical. I suggest you look into VBA (and retag your question accordingly). – Scott – 2013-07-13T00:14:03.990
Maybe if you explain what the ultimate goal of an operation like this is, we would be able to point you in the right direction. – Raystafarian – 2013-07-13T08:40:22.513
Im going to try to answer this but assume you cannot use the same cell more than once. – Scheballs – 2013-07-31T13:04:14.553
In addition to @Raystafarian's comment: tell us where this set of numbers comes from, that may help. Something must generate them, and the method or moment of generation might help to tackle the problem. – Jan Doggen – 2013-08-01T06:14:22.420