Return combos of #s located in col which add up to given #

[Andrea]

Example: I have 6 random numbers as follows:

2
3
5
9
4
1
8

I need to find a function that would tell me which number
(s) would add up to a given number. So, in this example,
if given the number 6, excel would return the numbers 2,
3, 1 and 4,2 and 1,5 and 6.

Is there any sort of function in excel which would allow
me to do this?

 

[andrea]

thanks, harlan.

-----Original Message-----
...

There's no 6 in your sample range.

There's no function in Excel that does this. To get all combinations, you'd need
to check all (2^7)-1 = 127 combinations of these numbers. Actually, heuristics
would allow you to exclude 9 and 8 from the combinations that add up to 6, so
there'd be only (2^5)-1 = 31 combinations to check.

The reason there's no function to do this is due to the nature of the problem.
If you had 20 numbers and the target value was greater than any of those values,
you'd need to check (2^20)-20-1 = 1,048,555 possible combinations. Excel would
become mighty slow trying to do so.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
.