# Rapid Solution of Optimal Control Problems by a Functional Spreadsheet Paradigm: A Practical Method for the Non-Programmer

## Abstract

**:**

## 1. Introduction

## 2. Mechanics of Spreadsheet Direct Method

- Initial value problem solver, IVSOLVE, using RADAU5 an implicit 5th-order Runge-Kutta algorithm with adaptive time step [15].
- Discrete data Integrator, QUADXY, using cubic splines [16].
- Discrete data differentiator, DERIVXY, using cubic splines [16].
- Formula integrator, QUADF, using Gauss quadrature with adaptive error control [17].

#### 2.1. Solution Strategy

#### 2.2. Convergence and Error Control

- Increasing the size of the data set by increasing the number of rows of the allocated IVP solution array to output a finer time grid.
- Supplying optional slopes at the end points of the curve to the calculus function when available. The slopes may be derived analytically from the integrand expression and can improve the accuracy of the spline fit near the curve edges.
- Using nonuniform output time points clustered near rapidly-varying regions of the state trajectories. This can be controlled via optional arguments to IVSOLVE including supplying exact values for the output time points.

## 3. Illustrative Optimal Control Problems

#### 3.1. Minimum Energy Shape: Hanging Chain

#### 3.1.1. Solution by Direct Spreadsheet Method

#### 3.1.2. Results and Analysis

#### 3.2. Quadratic Control Problem with Integral Constraint

#### 3.2.1. Solution by Direct Spreadsheet Method

#### 3.2.2. Results and Analysis

#### 3.3. Robot Motion Planning: Obstacle Avoidance

#### 3.3.1. Solution by Direct Spreadsheet Method

#### 3.3.2. Results and Analysis

#### 3.4. Nonlinear Bioprocess Optimization: Batch Production

#### 3.4.1. Solution by Direct Spreadsheet Method

#### 3.4.2. Results and Analysis

_{F}(B14), and the coefficients c_0, c_1 and c_2, (B8:B10) subject to the constraints:

## 4. Conclusions

## Supplementary Materials

## Funding

## Conflicts of Interest

## Appendix A

#### Appendix A.1. IVSOLVE: Initial Value Problem Solver

- 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(\mathit{x}\left(t\right),t\right)\right)$.
- Reference to the system variables in the specific order ($t,{x}_{1},{x}_{2},\dots ,{x}_{n}$).
- The integration time interval end points.

**Figure A2.**Partial listing of the result computed by IVSOLVE (

**left**) for system (A2), and a plot of the trajectories (

**right**).

#### Appendix A.2. QUADF: Formula Integrator Function

#### Appendix A.3. QUADXY: Discrete Data Integrator

#### Appendix A.4. DERIVXY: Discrete Data Differentiator

## References

- Geering, H.P. Optimal Control with Engineering Applications; Springer: Berlin, Germany, 2007. [Google Scholar]
- Sethi, S.P. Optimal Control Theory: Applications to Management Science and Economics, 3rd ed.; Springer: Berlin, Germany, 2019. [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]
- Betts, J.T. Practical Methods for Optimal Control and Estimation Using Nonlinear Programming, 2nd ed.; Advances in Design and Control; Society for Industrial and Applied Mathematics: Philadelphia, PA, USA, 2009. [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]
- Elnagar, G.; Kazemi, M.A. Pseudospectral Chebyshev Optimal Control of Constrained Nonlinear Dynamical Systems. Comput. Optim. Appl.
**1998**, 11, 195–217. [Google Scholar] [CrossRef] - 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.; Torres Monteiro, M.T.; Torres, D.F.M. Optimal Control and Numerical Software: An Overview. arXiv, 2014; arXiv:1401.7279. [Google Scholar]
- Ghaddar, C.K. Novel Spreadsheet Direct Method for Optimal Control Problems. Math. Comput. Appl.
**2018**, 23, 6. [Google Scholar] [CrossRef] - ExcelWorks LLC, MA, USA. ExceLab Calculus Add-in and Reference Manual. Available online: https://excel-works.com (accessed on 22 September 2018).
- Nævdal, E. Solving Continuous Time Optimal Control Problems with a Spreadsheet. J. Econ. Educ.
**2003**, 34, 2. [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] - 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. [Google Scholar] - Hairer, E.; Wanner, G. Solving Ordinary Differential Equations II: Stiff and Differential-Algebraic Problems; Springer Series in Computational Mathematics; Springer: Berlin, Germany, 1996. [Google Scholar]
- De Boor, C. A Practical Guide to Splines (Applied Mathematical Sciences); Springer: Berlin, Germany, 2001. [Google Scholar]
- Piessens, R.; de Doncker-Kapenga, E.; Ueberhuber, C.W.; Kahaner, D.K. Quadpack: A Subroutine Package for Automatic Integration; Springer: Berlin, Germany, 1983. [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] [Green Version] - Dolan, E.D.; More, J.J. Benchmarking Optimization Software with Cops; Technical Report; Argonne National Laboratory: Argonne, IL, USA, 2001. [Google Scholar]
- Lim, A.E.B.; Liu, Y.Q.; Teo, K.L.; B, M.J. Linear-quadratic optimal control with integral quadratic constraints. Optim. Control Appl. Methods
**1999**, 20, 79–92. [Google Scholar] [CrossRef] - Bhattacharya, R. OPTRAGEN 2.0: A MATLAB Toolbox for Optimal Trajectory Generation; Texas A & M University: College Station, TX, USA, 2013. [Google Scholar]
- Ghaddar, C.K. Rapid Modeling and Parameter Estimation of Partial Differential Algebraic Equations by a Functional Spreadsheet Paradigm. Math. Comput. Appl.
**2018**, 23, 39. [Google Scholar] [CrossRef]

**Figure 1.**Illustration of the ordered steps to define an analog formula for the cost index (1) which encapsulates the inner IVP (2)–(3).

**Figure 3.**Input to Excel solver for problem 3.1 based on the spreadsheet model in Figure 2.

**Figure 5.**Optimal u(t) computed using 3rd order parametrization for problem 3.1. Reported values by Dolan et al. are also shown.

**Figure 6.**Parametrized u(t) function is sampled with AutoFill to provide a handle on its minimum value for the purpose of imposing constraint (10).

**Figure 7.**Answer report generated by Excel solver using a 5th order parametrization for problem 3.1 with the added constrained (10).

**Figure 8.**Optimal u(t) computed by using 5th order parametrization for problem 3.1. The higher-cost solution with 3rd order parametrization and reported values by Dolan et al. are also shown.

**Figure 9.**Spreadsheet parametrized model for problem 3.2. The colored ranges are inputs for IVSOLVE formula (16).

**Figure 10.**Partial display of IVP (12)–(14) solution obtained by IVSOLVE formula (16), and dependent generated columns for the parametrized controls formulas, and the integrand expression for the cost index (11).

**Figure 13.**Direct comparison of spreadsheet solution with reported solution obtained by Lim et al. [20] for problem 3.2.

**Figure 14.**Spreadsheet parametrized model for problem 3.3. The colored ranges are inputs for IVSOLVE formula (24).

**Figure 15.**Partial display of the IVP (18)–(20) solution obtained by IVSOLVE formula (24), and dependent generated values needed to define the cost index and constraints formulas of problem 3.3.

**Figure 18.**Initial (

**a**) and optimal (

**b**) trajectories for problem 3.3 with additional constraint (26).

**Figure 20.**Spreadsheet parametrized model for problem 3.4. The colored ranges are inputs for IVSOLVE formula (37).

**Figure 21.**Partial display of IVP (28)–(34) solution obtained by IVSOLVE formula (37), and generated values for the parametrized control of problem 3.4.

**Figure 25.**Direct comparison of spreadsheet solution with reported solution obtained by Banga et al. for problem 3.4.

© 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.
Rapid Solution of Optimal Control Problems by a Functional Spreadsheet Paradigm: A Practical Method for the Non-Programmer. *Math. Comput. Appl.* **2018**, *23*, 54.
https://doi.org/10.3390/mca23040054

**AMA Style**

Ghaddar CK.
Rapid Solution of Optimal Control Problems by a Functional Spreadsheet Paradigm: A Practical Method for the Non-Programmer. *Mathematical and Computational Applications*. 2018; 23(4):54.
https://doi.org/10.3390/mca23040054

**Chicago/Turabian Style**

Ghaddar, Chahid Kamel.
2018. "Rapid Solution of Optimal Control Problems by a Functional Spreadsheet Paradigm: A Practical Method for the Non-Programmer" *Mathematical and Computational Applications* 23, no. 4: 54.
https://doi.org/10.3390/mca23040054