Design optimization using discrete decision variables

[abbe1992]

Hi!

I have a linear equation system that is based on a couple of input variables (x,y,z,w). 

The variables can only have certain values, e.g. x=[1 2 3 ... 50], y=[0.1 0.2 ... 20], z=[5 10 15 ... 50], w=[1.3 5.6 7.8 13.1 14.4]. The problem also consist of a couple of constraints and an objective. 

My solution so far can be described in the following way:

* Calculate the equation system for each combination of variables, one solution per column

* Remove the solutions where the constraints are not met

* Rank the remaining solutions

* Retrieve the input variables that correspond to the optimized solution (index match)

I think this is a simple way, but depending on the number of combinations (which is around 15-20000 for my actual problem), this can be a bit time-consuming and all of this data slows down the program. 

But then I found out about the Solver-function, which seemed to be perfect for me. Define decision variables, constraints and an objective. The only problem is that the decision variables can only be defined in different intervals as continuous values. Not a couple of discrete values based on a list, which is my case.

The scenario manager on the other hand, where I can define the combinations, seems pretty straight forward, but then I need to define 15-20000 scenarios..

Do anyone have a proposal of how to solve this problem in an efficient way? =) 

[#2 · Accepted solution]

@abbe1992 

Solver can constrain input variable to be whole numbers. You could use formulas to convert for example input 1, 2, ..., 50 to 0.1, 0.2, 50.0

Solver will use B2 to B5 as variables, but your target formula should refer to C2 to C5.

In the screenshot, F2:G5 is a lookup table for C5; the formula in C5 is =VLOOKUP(B5,F1:G5,2,FALSE)

[#3]

I really like the idea about using a lookup table for this problem. I will try this out!
Thank you very much!