How to find combination of cells that add to a set number

[abhi2611]

Hi,

Is there are way to find a combination of cells that add to a set number. I tried solver but, it only take 100 variables. I have more than 1000 records.

Eg.
I have the values in column A

1
2
0
-2
1
4

My target number is 3

I want to be able to find the combination of cell that add up to 3

Any help is appreciated.

Cheers!!

 

[Luke M]

Are you wanting to find all combinations, or just one combination that will work?
We could start by assuming that all the 0's can be included. For such a low target number, we can list the possible combinations (3+0, 2+1, 1+1+1) and do some COUNTIF type formula to see which one would work.

 

[abhi2611]

Luke,

Thank you for the reply.

I am trying to find all the different combinations. I have more than 1000 rows.

 

[Luke M]

I don't think that's possible to calculate on standard computers. With 1000 numbers, assuming that you can take a combination of either 1, 2, 3, ...999, or 1000 different numbers, there are over 1x10^301 combinations!

If we limit it to just looking at 1, 2, or 3 numbers in a combination, that's still 166,667,500 combinations. If we look at just 1 or 2 number combinations, it's 500,500 combinations (which might be doable).

 

[NARAYANK991]

Hi ,

My first question is are you giving data that is real-life data , or is it data that is at least representative data , or is it just made-up data which has no resemblance to your actual data ?

Secondly , what will the variation in the target value be ? For instance , you have given a value of 3 ; will this always be between the minimum and maximum of your data , or can it be any value whatsoever ?

Along the same lines , what will the variation in the data itself be ?

Lastly , will all the numbers be whole numbers or can there be decimal numbers in your data ?

Narayan