Formula to find x number of values in an array that sum XXX.

[stephjfischer]

I need a formula that will find x number of values in an array that add up to
xxx value.

 

[Frank Kabel]

Hi
how many numbers do you have in your array and do you need only one
valid solution or ALL possible solutions

 

[stephjfischer]

Depending on the situation, it could be as many as 50. Could be more, but
unneccessary cells could be filtered out to minimize the array.
All possible if that's possible.
Thank you in advance for your help whether you are able to come up with
something or not.

 

[hgrove]

stephjfischer wrote...

Depending on the situation, it could be as many as 50. Could be more but
unneccessary cells could be filtered out to minimize the array. Al possible if
that's possible.

...

The only a priori filtering could be eliminating numbers greater tha
your target sum, but that's only an option if all numbers are positive
If the numbers could be a mix of positives and negatives, you can'
eliminate any numbers.

You wind up with N numbers, and you'd need to check all 2^N -
combinations of those numbers to exhaust all possibilities. For N = 50
that's 1,125,899,906,842,623 possible combinations. If you coul
eliminate 1 billion combinations every second on average, it'd take 1
days to check all possibilities.

There are heuristics that can reduce this considerably, but only fro
days to hours.

As for finding a single solution, if you only have positive numbers
read the thread starting at

http://www.google.com/groups?threadm=#[email protected]

Solver is the only tool Excel provides that would do this with any hop
of efficiency. There is no reasonable formula-only approach