How To?????

[Gymclass14 via OfficeKB.com]

I am attempting to write a formula in excel 2003. What I am trying to
accomplish is to enter a list of values, say 20, 15, 4, 2.5, 1, 3, 6, 8.878,
17, 20.75, 6.25. Of these values, I want to be able to use the numbers that
get as close to a total of 40 as possible, without going over. The remaining
numbers would be used with an additional set of numbers to achieve the same
goal of getting as close to 40 as possible without going over. This would
continue until the last set of numbers would be used. Any ideas to get
started??

 

[Tom Hutchins]

You can find the solution each time using Solver (you will have to install
the Solver add-in if you haven't already). Supposing the set of numbers is in
A1:A11. Enter 1 in each cell in B1 to B11. Enter your target number (40) in
D1. Enter the following formula in some other cell (say F1):
=D1-SUMPRODUCT(A1:A11,B1:B11)

Now you are going to use the Solver to find the combination of numbers in
Column A whose total would be equal or closest to the number you have entered
in D1. Select Tools >> Solver in XL2003. In XL2007 Solver will appear on the
Data ribbon once you install it. In the "Solver Parameters" dialog,

"Set Target Cell" $F$1
"Equal To" Min
"By Changing Cells" $B$1:$B$11
"Subject to the Constraints" >> click Add >> enter $B$1:$B$11, select
"bin" from the dropdown list (this should add a constraint which reads as
"$B$1:$B$11=binary") >> Add >> enter $F$1, select >=, ener 0 for the
Constraint (this should add a constraint which reads as "$F$1>=0") >> OK.

Click "Solve"
The solver will find the solution by changing some of the 1's in column B
to 0's. The set of column A numbers for which column B is 1 (and not 0) is
the solution for your problem. If the solution is satisfactory, click "Keep
Solver Solution". Note that if more than one solution is possible, Solver
will find the first solution.

Include the remaining numbers (those for which column B is 0) in your next
set of numbers and run a new Solver to find the next solution.

Hope this helps,

Hutch