Excel Solver Finding Target Amount from a Subset of Numbers

Question

Excel Solver Finding Target Amount from a Subset of Numbers

I want to use Solver to find the target amount from a subset of a larger set. (10 subsets of 16 numbers). I'm not very good at Solver, and I don't know how to best set limits.

I have 5 categories of numbers in the set, A, B, C, D, E. A set of 16 can include various combinations of these categories, but the total number of the subset as a whole has a certain composition: A, B, B, C, C, C, (B or C), (B or C), D, E

So my question is: how do I introduce these rules in Solver?

Secondly, I know that Solver offers 1 solution, is there a way to get a series of solutions that are closest to the goal? So, if I have a set that does not quite create a goal, could I see 3 solutions that are closest to the goal (plus or minus)?

Thank.

0

excel-vba excel solver

Sfc k Jan 14 '17 at 17:51

source share

1 answer

OldUgly · Accepted Answer · 2017-01-15T07:02:21+0000

TL / DR . Here you can configure the problem. Getting a series of answers that are "close" is not available for Solver. You can do this with VBA if you define additional criteria for your search. (Edit - added setting for specific makeup)

Comments

There seems to be no criteria that “a number should be selected from each category” or “a number should be selected from category X”. Rather, you have a bunch of numbers, each of which belongs to a category, but want to find a combination of numbers that provide a certain amount - then explore the categories later.

Initial setup of the problem

, , ...