If you are looking for a single solution (Not necessarily the only one) to a
group of numbers that meet a target number, then this can be done quite simply
with Solver.
Assuming range of numbers in A1:A30, add a set of 0s in B1:B30 and in say B31
put
=SUMPRODUCT(A1:A30*B1:B30)
Now do Tools / Solver / Set Target Cell 'B31' to 'value of' and put in your
target
number. Then, using the range selector under the 'By Changing cells' section,
select cells B1:B30 as the ones to change and hit enter which will take you back
to the first dialog box. Now hit the 'Add' button, and add the constraint that
B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns,
so just hit the arrow and select 'bin') and just hit Solve. You MUST ensure
that in this example, when you add the 'bin' constraint range, you do not
inadvertantly include the formula cell B31, else you will get an error message
such as 'Binary Constraint cell reference must include only adjustable cells'
Won't do any more than single solution, but for a Finance Dept that will often
suffice in this context.
(Thanks to Henio as well for the following link, which will give you a helpful
tutorial at
http://www.solver.com/stepbystep.htm
and which walks you through an interesting scenario and explains what you can do
with the tool.)
If you are going to look for more than one target number in the data, then with
that formula in say B31, in B32 type the target number, and in B33 put =B32-B31.
Now have Solver solve B33 = to 0 with the same constraints. Saves having to
change any values in Solver that way, just type what you want in B32.
Looks quite neat too if you format B1:B30 to a white Font and then use
conditional formatting to highlight values in Col A where Val in Col B = 1
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------
Realwoodies said:
I am trying to find a way to start with a number and get a group of numbers
from a list that sum to the number. For example, if I have the # 30, and I have
a list {2,5,10,20}, I know that the numbers from the list that sum to 30 are 10
& 20. Is there a function in Excel that will do this for me?
