Tips for solving optimal control problems in Excel
-
Perhaps the most important factor is the starting guess for the unknown parameters vector which may require a nonzero initial value for some problems. It should be generally quick to find a good initial guess interactively by trial and error in just a few attempts given the fast response of the Solver.
-
Excel Solver's dialog offers a few settings, two of which are influential. In particular, the 'Derivatives' scheme is recommended to be switched to Central from the default Forward, and the 'Use Automatic Scaling' option is recommended to be left enabled (default setting).
-
Naming spreadsheet variables (e.g., naming B1 as t) makes the formulas easier to read and spot errors. However, it is also recommended to restrict the scope of a named variable to the specific sheet it will be used on, and not the whole workbook. This prevents accidental interdependence between multiple problems on different sheets sharing variables with the same name.
The shown layouts for the model setup with labels ensures that the Answer Report generated by Excel's Solver has proper descriptive names for the variables and constraints.
-
When using the operator ^ to compute powers, be ware that Excel evaluates the formula '=−X1^2' as '=(−X1)^2' when the intention may have been to do '−(X1^2)' instead. A simple fix is to either use parentheses when needed, or to use the intrinsic POWER() function instead of the operator ^.
-
When using the IF statement in a formula, it is important to verify that the formula evaluates to a numeric value for all possible conditions. Otherwise, the formula may evaluate to a nonnumeric Boolean condition, leading to a solver error.
-
The calculus functions are designed to operate in two modes: a silent mode, where only standard spreadsheet errors are returned like
#VALUE!, and a verbose mode, where the function may display an informative error or warning message alert in a popup window. It is recommended to work in the verbose mode when setting up the problem, but switch to the silent mode before running the Excel Solver. Switching between the two modes is triggered by evaluating the formula=VERBOSE(TRUE)or=VERBOSE(FALSE)in any cell in the workbook. For some problems, Excel Solver may wander into illegal space before it recovers and adjusts its search. The silent mode blocks any occasional error alerts from the calculus functions.
