Review every possible combination/permutation of the variables inputted into a function.

[rpaolillo]

I have 5 variables that input into a function. I want to review every possible combination/permutation of the variables and select the one that maximizes the output of this function.

To create every possible combination, the variables will have a minimum, maximum, and interval it will test. E.g. VAR1 can have a min value of 0, maximum value of 75, and will test each interval of 2.5. Additionally, I have a constraint flag. If the combination violates a constraint, the flag is FALSE, and the program should throw out the result.

The variables are also dependent on each other, so it couldn't simply loop thru each variable. It would have to loop through each variable combination. Thus the possible combinations is not 5x25 but 5^25

I am envisioning the program to loop through all the combinations and store the current maximum output and compare it to the current combination. If the current combination is greater than the maximum, it would throw out the old maximum combination and redefine the current combination as the maximum.

In excel there are 3 columns: StartingPoint (ColumnA), EndingPoint (ColumnB), Interval (ColumnC), where each row is its own variable. StartingPoint array is defined as A2:A6, EndingPoint array is defined as B2:B6, Interval array is defined as C2:C6. Constraint flag is in cell D2 (If false throw out the results). Variable results should be displayed in ColumnF with the maximized function in cell G2. The number of variables inputted into this function can vary so the code should have this flexibility.

Thanks in advance!

(I am very familiar with excel solver and simply dont like it. This solution simply picks the best one. Where as solver will get caught at a poor solving point depending on the path it takes.)

 

[jsotola]

sounds like a problem for prolog

 

[shg]

5^25 is a very large number. If VBA could evaluate 1M combinations/s, it would take 1300 years to ratchet through them all.

Plan B?

 

[rpaolillo]

function is very light and PC is top of the line. It can burn through alot of iterations in one sec. I can also increase the interval size decreasing the total combinations.

 

[rpaolillo]

function is very light and PC is top of the line. It can burn through alot of iterations in one sec. I can also increase the interval size decreasing the total combinations.

For example purposes, lets say min value of 0, maximum value of 50, and will test each interval of 25. Thus the number of results is only 5^3. That should be instantaneous.

 

[mikerickson]

Have you considered setting up a spreadsheet and using GoalSeek or Solver to find the maximum.

Analysis will find the optimal solution faster than brute force chugging through each permutation. (It will also get you an exact answer.)

Could you tell us the function (of five variables) that you are trying to maximize?

 

[rpaolillo]

I am already using solver. However, it yeilds different solutions depending on what paths it is takes. I basically run solver about 10 times using every option iteration. And I do get 10 different solutions, where as there is only one optimal solution.

I have created 5 different mathematical models that predict a number x. I am creating an weighted averaged of these models that bests fits the number x. Theory is that a portfolio of models will work best. I am trying to optimize the weighted average of these models. Simply, my function is an average or median of the errors of the portfolio of models vs X. This is just one example, I am using this methodology to solve several optimizations problems.

Solver arrives at a good solution. However, I would like to arrive at the optimal solution. Going thru these solver iterations in the my code already takes about 12 hours, so I am comfortable waiting 48 hours for a solution (its a bit more complicated than described above however, the function I described is approximately what I am trying to accomplish.)