Summing Program

[Guest]

Anybody ever run across a function, combination of functions or VBA code that
will go through a list of numbers and find a combination of those numbers
that equals a pre-designated total? For example, if I have the following
list of numbers:

5
2
3
7

I want to know the combinations of those numbers that is going to equal 10.
Anybody, ever run across or tried anything like this?

 

[Dave O]

I've written code to do exactly that. One of the consequences of this
"brute force" method of checking every possible combination of numbers
is that as the pool of numbers increases by one the number of
combinations doubles. This is fine and grand when the pool is twenty
numbers, but the jump from 24 numbers in the pool (roughly 16.8 million
combinations) to 25 numbers (about 33.5 million combinations) causes
the processing time to increase.

What is your application? This software is not ready for prime time in
terms of saleabililty, but I'd be happy to run it for you on a one-ff
basis. Contact me at cyclezen AT yahoo DOT com.

 

[Guest]

Thanks Dave 0. I was going to attempt the code myself, but I wanted to see
if there was anybody that had tried it. I have a lot of numbers, close to
1,000 so I think that I would be wasting yours and my time. Also, I'm not
really sure that it would get me closer to the answer I'm looking for. I
appreciate your help though. Thanks again!

PS - I'm sure someone besides just you and me have needed this. I guess the
reason it never made it to the Excel built-in functions is because of the
calculation time.

 

[Dave O]

When I plug 2 to the 1000th power into my calculator I get a scientific
notation response equivalent to
9,999,999,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
possible combinations. We'd need a quantuum improvement in processor
ability to even consider this.

Is it possible to pare down the number pool? For instance, if there
are no negative numbers in the pool, then any number greater than the
target number can be removed from the pool. There may also be other
ways to reduce the pool: for instance, if your target number has to do
with a list of checks, say, written before June 1 and some of the pool
numbers are from July, they can be removed.