Calculating perfect combination of numbers to end up as close to 1700.0as possible

[chris abbink]

Hello,

I'm stuck with a problem. I use the following file:
http://imageshack.us/photo/my-images...culations.jpg/
(which works perfectly)

The file uses MAX 15 different numbers (as seen in the IgG (g) column, 2 left of the red circled column).
Basicly what I want excel to do is to calculate all different possible combinations from that list and tell me which batches (1 till 15) I would have to use to come as close to 1700 gram in total as possible.

Like if I use the numbers 1 till 14, I would get a total of 1675.7 gram. Which comes pretty close to 1700, but there is probably a combination which comes closer to 1700. I want that combination

This problem is way beyond my excel/VBA skills and would very much appreciate if someone could help me out or point me in the right direction.

 

[Jim Cone]

Take a look here...
http://www.tushar-mehta.com/excel/templates/match_values/index.html
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
Special Sort Excel add-in (30 ways to sort)



"chris abbink" <[email protected]>
wrote in message

Hello, -snip-
what I want excel to do is to calculate all different possible combinations
from that list and tell me which batches (1 till 15) I would have to use to come
as close to 1700 gram in total as possible.

-snip-

 

[chris abbink]

Thanks for replying!

I indeed found that link too.
It does exactly what I want, but only for exact numbers. For example, if the most perfect batch I could make was 1679.5 grams, this macro wouldn't find it for me.

To make use of this macro I could try something like this:
If I can alter the macro so that if a combination of 1700.0 isn't found, it does the target amount (1700.0) minus 0.1. So it would look for a combination of 1699.9. And loop that untill a match is found. For example 1657.3.

But I have no clue on how to integrate this into that macro.

 

[Jim Cone]

Your link returns a 404 (not found) error and it has been years since I used solver.
So... if exact numbers are needed, try inserting another column containing your data multiplied by
ten.
Note: changing the post's subject line can screw up future searches.