Solving Dynamical Optimization Problems in Excel

You can combine ExceLab calculus functions with either native Excel Solver or NLSOLVE to solve a variety of parameter estimation and dynamical optimization problems.

If you have learned how to obtain a solution with the calculus functions, you are almost done! Setting up a parameter or dynamical optimization problem is straightforward with just a few steps:

  1. Solve your model as you would normally do using the relevant calculus solvers. Make sure to use variables with initial reasonable values for the model parameters you want to optimize instead of just hardcoding their values into your model formulas.

    Your design parameters can be any parameters that influence the system behavior including coefficients, bcs, initial values, etc.

  2. The next step is to define constraint formulas to penalize the deviation of your initial solution from your target value.

    • If your initial solution is a single value (e.g., an integral evaluation) you can directly define a constraint formula on the obtained value.
    • On the other hand, if your initial solution is an array value (e.g., and ODE or PDE solution), then you must use a Criterion Function to define a single-valued constraint on the array value. You can define as many constraints you need.

  3. The final step is to solve for the optimal model parameters that will satisfy your constraints formula (i.e., drive their values to zero). You have two choices:

    • If you do not have a cost formula to maximize or minimize, use NLSOLVE to solve the system of constraints formulas for the unknown model parameters.
    • On the other hand, if you do have a cost formula, then use Excel Solver to setup an optimization problem to minimize or maximize your cost formula subject to the constraint formulas you have defined.

The best way to learn is by viewing the examples.

Question or Comment? Email us:
support @ excel-works.com
ExceLab: Transforming Excel into a Calculus Power House
ExceLab functions and methods are protected by USA Patents 9286286, 9892108, 10114812 and pending.
© 2015-2019, ExcelWorks LLC
Boston, USA