as you point out, this method does not find all solutions - only 1 if one
exists. I assume one could write an algorithm that used it against subsets
of the data to find additional - but to find all possible, I would think one
would need to intelligently enumerate all feasable possibilities and test
them.
--
Regards,
Tom Ogilvy
"Paul Corrado" wrote in message
...
I did some testing, (picked 12 rows that included 0's and 1's from the
first
solution, and deleted them). and found that there is an alternative
solution. So, while this method will return a solution, it is not
necessarily "the" solution. Data below also sums to $1391.03. I guess
the
shortcoming results from the sheer number of possible combinations
(4.61169E+18).
29.55 1
26.77 1
46.94 0
5.99 1
16.8 1
15.93 1
20.56 1
13.57 1
4.44 0
21.69 1
97.48 1
19.66 1
51.25 1
11.77 1
14.31 1
5.98 0
9.2 0
11.37 1
12.55 1
3.64 1
17.01 1
50.59 1
14.3 1
3 1
109.53 1
154.88 1
13.07 1
22.65 1
11.37 1
32.84 1
42.23 1
23.6 1
0.26 0
1.03 1
21.75 1
17.97 1
22.47 1
34 1
120 1
63.55 1
3.68 1
75.46 1
26.98 1
15.88 1
8.86 1
30.1 1
25 1
23.7 1
19.97 1
12.67 1
PC
"Tom Ogilvy" wrote in message
...
I arranged your data like this (A1:B62) (this is actually the
solution).
Each number was in column A and column B contained 1's (B1:B62) (I
took
out any rows with spaces).
I created defined names
Insert=Name=Define
Name: Data
Refers to: =Sheet1!$A$1!$A$62
Name: Flag
Refers to: =Sheet1!$B$1!$B$62
In D1 I put in the formula
=SumProduct(Data,Flag)
I then did Tools=solver. I selected
Set Target Cell: = *$D$1*
Equal to: Value of *1391.03*
By Changing Cells *Flag*
I clicked Add Constraints
In the first box I put *Flag* and selected *bin* from the dropdown
I then told solver to solve (Clicked the solve button) in the below,
the
numbers with a 1 next to them sum up to 1391.03
29.55 1
26.77 1
46.94 0
5.99 1
16.8 1
15.93 1
20.56 1
13.57 1
4.44 1
21.69 1
97.48 0
24.15 1
3.38 1
133.48 0
48.82 1
1.94 0
24.15 1
126.1 1
71.83 1
32.92 1
46.57 1
88.12 1
132.49 0
19.66 1
51.25 1
11.77 1
14.31 1
5.98 1
9.2 1
11.37 1
12.55 1
3.64 1
17.01 1
50.59 1
14.3 1
3 0
109.53 0
154.88 0
13.07 1
22.65 1
11.37 1
32.84 1
42.23 1
23.6 1
0.26 1
1.03 0
21.75 1
17.97 1
22.47 1
34 1
120 0
63.55 1
3.68 1
75.46 1
26.98 1
15.88 1
8.86 1
30.1 1
25 1
23.7 1
19.97 1
12.67 1
--
Regards,
Tom Ogilvy
Tony Johnson wrote in message
...
Thanks for taking the time to read this. Here is my question Lets say
I
have
a column of numbers to choose from to equal a certian amount. Problem
is
I
do not know how many of these I need to add together to get the
required
number. Here is an example
29.55
26.77
46.94
5.99
16.8
15.93
20.56
13.57
4.44
21.69
97.48
24.15
3.38
133.48
48.82
1.94
24.15
126.1
71.83
32.92
46.57
88.12
132.49
19.66
51.25
11.77
14.31
5.98
9.2
11.37
12.55
3.64
17.01
50.59
14.3
3
109.53
154.88
13.07
22.65
11.37
32.84
42.23
23.6
0.26
1.03
21.75
17.97
22.47
34
120
63.55
3.68
75.46
26.98
15.88
8.86
30.1
25
23.7
19.97
12.67
I have to figure out what combination of those numbers equals 1391.03
Is there a way for excel to choose random combinations till it finds
the
answer?
Thanks in advance