Hello everyone,
I am trying to use the Excel Solver to optimize 3 variables (smoothing parameters) for a statistical forecast model. In the attached file, you will see that I set up the Objective Function (minimize) in column AM. I have the 3 variables in columns AN, AO, and AP. The VBA code uses a "For" with a nested "If" to loop the Solver to execute for each row individually. You will see I have 17 rows it loops through, so it must find a solution for 17 problems when executing the macro.
I have two questions, both pertain to the SolverOptions function of the Solver:- You can see in my attached file that the Solver violates the lower and upper bounds I set for the variables. Why is this? Are theer SolverOptions settings I can tailor to ensure the solution is within the defined bounds?
- The Solver runs through these problems very quickly. I fear that it is not converging on the "optimal" solutions (variable values). Again, are there SolverOptions I can set to ensure I am close to the Optimal? (I am trying to achieve variable values to the nearest hundreth (+/-0.01).
Also, here is the VBA:
example.xlsm
Bookmarks