# Novel Spreadsheet Direct Method for Optimal Control Problems

## Abstract

**:**

## 1. Introduction

- A bang–bang control problem.
- A highly nonlinear and coupled system.
- A minimum swing container transfer problem involving multiple controls and constraints.
- A minimum time orbit transfer control problem with free end time.

## 2. Spreadsheet-Adapted Direct Solution Method

**Task 1**

**Task 2**

**Task 3**

#### How It Works

## 3. Illustrative Examples

#### 3.1. A Bang–Bang Control Problem

#### 3.1.1. Spreadsheet Model

**u**and

**Integrand**(see Figure 4) for the control function and the integrand expression. The control column,

**u**, was generated with the AutoFill feature of Excel, using the formula H3 shown in Table 1. The integrand column was generated in a similar way using the formula J3 in Table 1. Here, we simply evaluated the expression $x{\left(t\right)}^{2}+y{\left(t\right)}^{2}$ using the corresponding output solution values for t, x, and y from the IVSOLVE solution.

#### 3.1.2. Results and Discussion

#### 3.2. Unconstrained Nonlinear Optimal Control Problem

#### 3.2.1. Spreadsheet Model

^{18}, as shown in Figure 9.

#### 3.2.2. Results and Discussion

#### 3.3. Minimal Swing Container Transfer Problem

#### 3.3.1. Spreadsheet Model

#### 3.3.2. Results and Discussion

^{−5}, except for x_6(1), which was satisfied within a tolerance of 1 × 10

^{−3}. This is verified by the feasibility report generated by the Solver, and shown in Figure 19. The report indicates that the Solver had difficulty satisfying end point constraints for x_4 and x_6 at the Solver’s default tolerances, while all other constraints were satisfied.

#### 3.4. Minimum Time Orbit Transfer Problem

#### 3.4.1. Spreadsheet Model

#### 3.4.2. Results and Discussion

_{F}, B13, and the control coefficients B15:B18, subject to the end point equality constraints on the state variables (36). The constraints were added directly into the Solver’s dialog by referencing the corresponding cells in the last row of the IVSOLVE solution array in Figure 21. The Solver reported, in under 20 s, the feasible solution shown in the Answer Report of Figure 22. The minimum orbit time, ${t}_{F}$, was found to be 3.58656. This compares reasonably well to the value reported in [17] at 3.31873 using ninth-degree Chebyshev polynomial approximations. The optimal trajectories are plotted in Figure 23b. In Figure 24, we show a partial listing of the updated IVSOLVE solution result reflecting the new end time, and the decreased output time increment in comparison to the initial result shown in Figure 21.

## 4. Practical Tips

#### 4.1. Excel’s NLP Solver and Settings

#### 4.2. Spreadsheet Tips

- 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.
- Excel gives precedence to the unary negation operator which may be confused with the binary minus operator since they both use the same symbol. This can lead to hard-to-find errors in formulas. For example, Excel evaluates the formula ‘=−X1^2’ as ‘=(−X1)^2’. 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(X1,2) function instead of the operator ^. Also, 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 NLP 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, the Solver may wander into illegal input space before it recovers and adjusts its search. The silent mode blocks any occasional error alerts from the calculus functions.

#### 4.3. Generalization to a Special Class of Control Problems

## 5. Conclusions

## Supplementary Materials

## Acknowledgments

## Conflicts of Interest

## Appendix A

#### Appendix A.1. Initial Value Problem Solver Spreadsheet Function

- Reference to the right-hand side formulas corresponding to the vector-valued function $F\left(\mathit{x}\left(t\right),t\right)=\left({f}_{1}\left(\mathit{x}\left(t\right),t\right),{f}_{2}\left(\mathit{x}\left(t\right),t\right),\dots ,{f}_{n}\left(x\left(t\right),t\right)\right)$. Any algebraic equations should be ordered last.
- Reference to the system variables corresponding to t and $\mathit{x}\left(t\right)$ in the specific order ($t,\text{}{x}_{1},\text{}{x}_{2},\text{}\dots ,{x}_{n}$).
- The integration time interval end points.

**Figure A2.**(

**a**) Partial listing of the computed solution by formula A3; (

**b**) Plots of the trajectories.

#### Appendix A.2. Discrete Data Integrator Spreadsheet Function

## References

- La Torre, D.; Kunze, H.; Ruiz-Galan, M.; Malik, T.; Marsiglio, S. Optimal Control: Theory and Application to Science, Engineering, and Social Sciences. Abstr. Appl. Anal.
**2015**, 2015, 890527. [Google Scholar] [CrossRef] - Geering, H.P. Optimal Control with Engineering Applications; Springer: Berlin/Heidelberg, Germany, 2007. [Google Scholar]
- Aniţa, S.; Arnăutu, V.; Capasso, V. An Introduction to Optimal Control Problems in Life Sciences and Economics: From Mathematical Models to Numerical Simulation with MATLAB; Birkhäuser: Basel, Switzerland, 2011. [Google Scholar]
- Böhme, T.J.; Frank, B. Indirect Methods for Optimal Control. In Hybrid Systems, Optimal Control and Hybrid Vehicles. Advances in Industrial Control; Springer: Cham, Switzerland, 2017. [Google Scholar]
- Böhme, T.J.; Frank, B. Direct Methods for Optimal Control. In Hybrid Systems, Optimal Control and Hybrid Vehicles. Advances in Industrial Control; Springer: Cham, Switzerland, 2017. [Google Scholar]
- Banga, J.R.; Balsa-Canto, E.; Moles, C.G.; Alonso, A.A. Dynamic optimization of bioprocesses: Efficient and robust numerical strategies. J. Biotechnol.
**2003**, 117, 407–419. [Google Scholar] [CrossRef] [PubMed] - Rodrigues, H.S.; Monteiro, M.T.T.; Torres, D.F.M. Optimal Control and Numerical Software: An Overview. This is a preprint of a paper whose final and definite form will appear in the book. In Systems Theory: Perspectives, Applications and Developments; Miranda, F., Ed.; Nova Science Publishers: Hauppauge, NY, USA, 2014; Available online: https://arxiv.org/abs/1401.7279 (accessed on 23 January 2018).
- Ghaddar, C. Method, Apparatus, and Computer Program Product for Optimizing Parameterized Models Using Functional Paradigm of Spreadsheet Software. U.S. Patent 9,286,286, 15 March 2016. [Google Scholar]
- Ghaddar, C. Method, Apparatus, and Computer Program Product for Solving Equation System Models Using Spreadsheet Software. U.S. Patent 15,003,848, 2018. in Press. [Google Scholar]
- Ghaddar, C. Unconventional Calculus Spreadsheet Functions. World Academy of Science, Engineering and Technology, International Science Index 112. Int. J. Math. Comput. Phys. Electr. Comput. Eng.
**2016**, 10, 194–200. Available online: http://waset.org/publications/10004374 (accessed on 23 January 2018). - Ghaddar, C. Unlocking the Spreadsheet Utility for Calculus: A Pure Worksheet Solver for Differential Equations. Spreadsheets Educ.
**2016**, 9, 5. Available online: http://epublications.bond.edu.au/ejsie/vol9/iss1/5 (accessed on 23 January 2018). - Hairer, E.; Wanner, G. Solving Ordinary Differential Equations II: Stiff and Differential-Algebraic Problems; Springer Series in Computational Mathematics; Springer: Berlin/Heidelberg, Germany, 1996. [Google Scholar]
- De Boor, C. A Practical Guide to Splines (Applied Mathematical Sciences); Springer: Berlin/Heidelberg, Germany, 2001. [Google Scholar]
- Lasdon, L.S.; Waren, A.D.; Jain, A.; Ratner, M. Design and Testing of a Generalized Reduced Gradient Code for Nonlinear Programming. ACM Trans. Math. Softw.
**1978**, 4, 34–50. [Google Scholar] [CrossRef] - Weber, E.J. Optimal Control Theory for Undergraduates Using the Microsoft Excel Solver Tool. Comput. High. Educ. Econ. Rev.
**2007**, 19, 4–15. [Google Scholar] - Nævdal, E. Solving Continuous Time Optimal Control Problems with a Spreadsheet. J. Econ. Educ.
**2003**, 34, 99–122. [Google Scholar] [CrossRef] - Elnagar, G.; Kazemi, M.A. Pseudospectral Chebyshev Optimal Control of Constrained Nonlinear Dynamical Systems. Comput. Optim. Appl.
**1998**, 11, 195–217. [Google Scholar] [CrossRef] - FrontlineSolvers. Available online: https://www.solver.com/ (accessed on 23 January 2018).
- ExcelWorks LLC; MA, USA. ExceLab Calculus Add-in for Excel and Reference Manual. Available online: https://excel-works.com (accessed on 23 January 2018).
- Hindmarsh, A.C. ODEPACK, A Systematized Collection of ODE Solvers. In Scientific Computing; Stepleman, R.S., Carver, M., Peskin, R., Ames, W.F., Vichnevetsky, R., Eds.; North-Holland Publishing: Amsterdam, The Netherlands, 1983; pp. 55–64. [Google Scholar]

**Figure 2.**Illustration of the ordered steps for defining an analogous objective formula to the cost index functional (1). The illustration assumes a problem with two state variables and one control.

**Figure 3.**Spreadsheet model for IVP (7)–(9) with parametrized control function. The colored ranges are input parameters for the IVSOLVE Formula (11).

**Figure 4.**Partial listing of computed results by Formula (11). Also shown are generated control and integrand columns, and initial objective formula value. The associated formulas are listed in Table 1.

**Figure 7.**(

**a**) Initial trajectories for optimal control problem (6)–(10) based on the default values shown in Figure 3; (

**b**) Optimal trajectories found by Excel’s nonlinear programming (NLP) Solver.

**Figure 8.**Spreadsheet model for the IVP (13)–(15) with parametrized control function. The colored ranges are input parameters for IVSOLVE Formula (16).

**Figure 9.**Partial listing of computed results by Formula (16). Also shown are generated control and integrand columns, and the initial objective formula value. The associated formulas are listed in Table 2.

**Figure 10.**(

**a**) Initial trajectories for optimal control problem (12)–(15) based on the default values shown in Figure 8; (

**b**) Optimal trajectories found by Excel’s NLP Solver.

**Figure 11.**Answer Report generated by Excel’s Solver for optimal control problem (12)–(15) based on the initial guess values in Figure 8.

**Figure 12.**Answer Report for optimal control problem (12)–(15) using a different initial guess and yielding improved minimum.

**Figure 13.**Optimal trajectories for optimal control problem (12)–(15) found by Excel’s Solver starting from a different initial guess, leading to a lower objective value.

**Figure 14.**Spreadsheet model for the IVP (18)–(24) with parametrized control functions. The colored ranges are input parameters for IVSOLVE Formula (30).

**Figure 15.**Partial listing of computed result by Formula (30). Also shown are generated control and integrand columns. The associated formulas are listed in Table 3.

**Figure 16.**Initial trajectories for optimal control problem (17)–(29) based on default values shown in Figure 14.

**Figure 18.**(

**a**) Optimal trajectories for all variables of optimal control problem (17)–(29); (

**b**) Selected optimal trajectories.

**Figure 20.**Spreadsheet model for the IVP (32)–(35) with parametrized control function. The colored ranges are input parameters for IVSOLVE Formula (37).

**Figure 21.**Partial listing of computed result by Formula (37). Also shown are generated control column and the initial objective formula value. The associated formulas are listed in Table 5.

**Figure 23.**(

**a**) Initial trajectories for optimal control problem (31)–(36) based on default values shown in Figure 20; (

**b**) Optimal trajectories found by Excel’s NLP Solver.

**Figure 24.**Partial listing of the updated initial result of Figure 21 which reflects the optimal final time and adjusted output time values in Column E.

Purpose | Cell | Formula |
---|---|---|

Initial value problem solution | D2:F103 | =IVSOLVE(B11:B12,B2:B4,{0,5}) |

AutoFill formula for control values | H3 | =IF(D3<=switchT,stage1,stage2) |

AutoFill formula for integrand values | J3 | =E3^2+F3^2 |

Objective formula | M3 | =0.5*QUADXY(D3:D103,J3:J103) |

Maximum value of control column | M6 | =MAXA(H3:H103) |

Minimum value of control column | M7 | =MINA(H3:H103) |

Purpose | Cell | Formula |
---|---|---|

Initial value problem solution | E2:G103 | =IVSOLVE(B12:B13,B2:B4,{−1,1}) |

AutoFill formula for control values | I3 | =c_0+c_1*E3+c_2*E3^2+c_3*E3^3 |

AutoFill formula for integrand values | K3 | =F3^2+G3^2+0.1*I3^2 |

Objective | N3 | =0.78*QUADXY(E3:E103,K3:K103)/2 |

Purpose | Cell | Formula |
---|---|---|

Initial value problem solution | F2:L103 | =IVSOLVE(B17:B22,B2:B8,{0,1}) |

AutoFill formula for u_1 control values | N3 | =c_0+c_1*F3+c_2*F3^2+c_3*F3^3 |

AutoFill formula for u_2 control values | O3 | =d_0+d_1*F3+d_2*F3^2+d_3*F3^3 |

AutoFill formula for integrand values | P3 | =I3^2+L3^2 |

Objective Formula | S3 | =4.5*QUADXY(F3:F103,P3:P103) |

u_1 column max value | S6 | =MAXA(N3:N103) |

u_1 column min value | S7 | =MINA(N3:N103) |

u_2 column max value | S8 | =MAXA(O3:O103) |

u_2 column min value | S9 | =MINA(O3:O103) |

x_4 column max value | S10 | =MAXA(J3:J103) |

x_4 column min value | S11 | =MINA(J3:J103) |

x_5 column max value | S12 | =MAXA(K3:K103) |

x_5 column min value | S13 | =MINA(K3:K103) |

**Table 4.**List of constraints added to the NLP Solver for optimal control problem (17)–(29) and their corresponding equations. The bound constraints were defined using aid formulas listed in Table 3.

Added Constraints | Purpose |
---|---|

G103 = 10 | ${x}_{1}\left(1\right)=10$ |

H103 = 14 | ${x}_{2}\left(1\right)=14$ |

I103 = 0 | ${x}_{3}\left(1\right)=0$ |

J103 = 2.5 | ${x}_{4}\left(1\right)=2.5$ |

K103 = 0 | ${x}_{5}\left(1\right)=0$ |

L103 = 2.5 | ${x}_{6}\left(1\right)=0$ |

S10 ≤ 2.5 | (4.12) |

S11 ≥ −2.5 | |

S12 ≤ 1 | (4.13) |

S13 ≥ −1 | |

S6 ≤ 2.83374 | (4.10) |

S7 ≥ −2.83374 | |

S8 ≤ 0.71265 | (4.11) |

S9 ≥ −0.80865 |

Purpose | Cell | Formula |
---|---|---|

Initial value problem solution | E2:H103 | =IVSOLVE(B21:B23,B2:B5,B12:B13) |

AutoFill formula for control values | J3 | =c_0+c_1*E3+c_2*E3^2+c_3*E3^3 |

Objective formula | L3 | =t_{F} |

© 2018 by the author. Licensee MDPI, Basel, Switzerland. This article is an open access article distributed under the terms and conditions of the Creative Commons Attribution (CC BY) license (http://creativecommons.org/licenses/by/4.0/).

## Share and Cite

**MDPI and ACS Style**

Ghaddar, C.K.
Novel Spreadsheet Direct Method for Optimal Control Problems. *Math. Comput. Appl.* **2018**, *23*, 6.
https://doi.org/10.3390/mca23010006

**AMA Style**

Ghaddar CK.
Novel Spreadsheet Direct Method for Optimal Control Problems. *Mathematical and Computational Applications*. 2018; 23(1):6.
https://doi.org/10.3390/mca23010006

**Chicago/Turabian Style**

Ghaddar, Chahid Kamel.
2018. "Novel Spreadsheet Direct Method for Optimal Control Problems" *Mathematical and Computational Applications* 23, no. 1: 6.
https://doi.org/10.3390/mca23010006