How do I get Excel to search an array of numbers and find only those numbers
that sum to a certain total? Which of the following numbers (3,5,7,14,20)
total 22? Answer: 3, 5, and 14 total 22

tommydancer wrote...
How do I get Excel to search an array of numbers and find only those numbers
that sum to a certain total? Which of the following numbers (3,5,7,14,20)
total 22? Answer: 3, 5, and 14 total 22

This is a classic programming problem. The answer is that there's no
guaranteed way to find the solution to this problem that doesn't
require iterating through all possible subsets of the individual
numbers. And if there are N individual numbers, there are 2^N-1
nontrivial subsets. For N-5, that means only checking 31 subsets of the
numbers. For N=20, there's over a million. For N=100, you'd need months
of uninterrupted supercomputer time.

Actually, that overstates the problem a bit, but only a bit. Heuristics
can be applied to limit the subsets, but that only eliminates a few
orders of magnitude.

Use Google Groups advanced search to find previous responses to this
sort of question. You could use the search words SUMPRODUCT and SOLVER
as well as the search phrase "sum to" to find solutions that'd give one
possible combination. If you want all such combinations, you'll need to
use VBA, and it could take a LONG time, long as in hours. Solutions
that'd require days or longer would exceed system resources long before
that.

Assuming your data on column A
A1=3
A2=5
A3=7
A4=14
A5=20
On column B
B1=1
B2=1
B3=1
B4=1
B5=1
On column C
A1*B1
A2*B2 so on....

enter formula on cell C6 =SUM(C1:C5)
Tool | Solver | Set Target Cell: $C$6
Equal To: Select Value of: 22
By Changing Cells: $B$1:$B$5 click Add
Change Constraint Cell Reference: $B$1:$B$5 also change <= to int then click
Add
Click Solve, Keep Sover Solution, click OK


"tommydancer" wrote:

How do I get Excel to search an array of numbers and find only those numbers
that sum to a certain total? Which of the following numbers (3,5,7,14,20)
total 22? Answer: 3, 5, and 14 total 22

In article ,
says...
How do I get Excel to search an array of numbers and find only those numbers
that sum to a certain total? Which of the following numbers (3,5,7,14,20)
total 22? Answer: 3, 5, and 14 total 22

For a template, see
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/ti...set-match.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions