To do this, you would create one binary variable for each variable you have and for each value you want that variable to take. For example, say you have 5 variables and 5 different values that each variable can take, set-up so the variables values are in A1:A5 and the variable cells haven't been assigned yet.
In B1:B5 you can set binary variables, and in B6:B10 you set =A1 * B1 matching the rows as you go down (so A1 * B1 is in B6, A2 * B2 is in B7 etc, etc).
You would then have to do the same for the next 4 variables and ranges (if they have different ranges, this gets a bit more complicated).
Note you will need one more thing: You want to SUM your binary variables that are associated with a single variable, and set a constraint that, that SUM must = 1, I.E. the constraint on a cell that contains SUM(B6:B10) = 1. This ensures that solver uses one of the values in the list of available values, no more, and no less.
From there the rest depends on your objective function, if you want a simple SUM you can SUM(B6:<end of binary multiplications>) in your objective, and Solver will add a bunch of 0's and one actual value, based on the set-up above.
We understand that the instructions above may not be the easiest to follow, so we set-up a very simple "mock" example that shows the above set-up with a very simple objective function, the numbers and functions used are "meaningless" in this case as everything in the workbook is arbitrary.
With the way the workbook it set-up, the bottom row of binaries will always be chosen (since the objective is set to maximize at the moment). If you change the objective to minimize the top row will be chosen. You can further adjust the available values for variables and the objective function to get different results, but the idea will be the same as that displayed in the workbook. Note that you will not "see" the iterations, but Solver is testing every possible combination of binary values in its solving process (at least, very close to it) in order to "prove" the optimal solution.
Comments
0 comments
Please sign in to leave a comment.