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:
Sub Macro()
'Initialize variables
Dim lngMyRow As Long
Dim lngLastRow As Long
'Find where the last row currently resides
lngLastRow = Range("AM:AM").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
'Beginning on row 33
For lngMyRow = 33 To lngLastRow
'IF statement to run this 1st portion only if "AL & lngMyRow" = Y, otherwise, skip:
If InStr(1, Range("$AL$" & lngMyRow).Value, "Y") Then
'Reset the Solver for Trend Seasonal model
Application.Run "SolverReset"
'Include the constraint that the cell value must be less than or equal to 0.99 (1 means "less than or equal to")
Application.Run "SolverAdd", "$AN$" & lngMyRow & ":$AN$" & lngMyRow, 1, ":$AN$30"
Application.Run "SolverAdd", "$AO$" & lngMyRow & ":$AO$" & lngMyRow, 1, ":$AO$30"
Application.Run "SolverAdd", "$AP$" & lngMyRow & ":$AP$" & lngMyRow, 1, ":$AP$30"
'Include the constraint that the cell value must be greater than or equal to 0.01 (3 means "greater than or equal to")
Application.Run "SolverAdd", "$AN$" & lngMyRow & ":$AN$" & lngMyRow, 3, ":$AN$29"
Application.Run "SolverAdd", "$AO$" & lngMyRow & ":$AO$" & lngMyRow, 3, ":$AO$29"
Application.Run "SolverAdd", "$AP$" & lngMyRow & ":$AP$" & lngMyRow, 3, ":$AP$29"
'Solve to minimize the objective function (the cell containing MAPE) (2 means "minimize)
Application.Run "SolverOk", "$AM$" & lngMyRow, 2, "0", "$AN$" & lngMyRow & ":$AP$" & lngMyRow
Application.Run "SolverSolve", True
End If
Next lngMyRow
Application.ScreenUpdating = True
End Sub
example.xlsm
Bookmarks