A Bang-Bang Control Problem

The first example describes a bang-bang (two-stage) control problem . The mathematical problem is stated as follows:



subject to





Spreadsheet Model

Working with named variables shown in Table 1, we parametrized the two-stage control function, u(t), using a standard IFstatement, as shown in B9. The unknown parameters switchT, stage1, and stage2 are assigned the initial guess values 0.1, 0, and 1. We define the formulas for the ODE equations (2) and (3) in B11 and B12, and assign the initail values (4) to x and y in in B3 and B4.

Table 1
1ODE variables
5Parametrized control formula
10ODE rhs formulas

We obtain a solution to the inner initial value problem by evaluating the array formula =IVSOLVE(B11:B12, B2:B4, {0,5}) in an allocated array D2:F103. The result is shown partially in Table 2, and the initial trajectories of x(t), y(t), and u(t) are plotted in Figure 1.

Table 2
1IVP Solution        
2txyu Integrand Cost functional
50.10.3480321.2142401.595504Constraint formulas
Table 3: formulas used to generate values shown in Table 2
Initial value problem solutionD2:F103=IVSOLVE(B11:B12, B2:B4, {0,5})
AutoFill formula for control valuesH3=IF(D3<=switchT, stage1, stage2)
AutoFill formula for integrand valuesJ3=E3^2+F3^2
Objective formulaM3=0.5*QUADXY(D3:D103, J3:J103)
Maximum value of control columnM6=MAXA(H3:H103)
Minimum value of control columnM7=MINA(H3:H103)
Figure 1

To define the objective formula for the cost functional (1), we generate, based on the obtained initial solution in Table 2, two columns for the control function and the integrand expression using formulas H3 and J3 shown in Table 3. Next, we define the objective formula M3 in which we use QUADXY to integrate the integrand column versus the solution output times column. The initial value of the objective formula is 246.0854, as shown in Table 2.

To impose the bound constraint (5) on u(t), we defined two aid formulas in M6 and M7 which computed the maximum and minimum of the generated control column values. We make use of these aid formulas during the configuration of Excel Solver.

Results and Discussion

We configure Excel Solver as shown in Figure 2 to minimize the objective formula M3 by varying the control parameters B6:B8 (corresponding to switchT, stage1 and stage2) subject to the constraints

M6 ≤ 0.8 corresponds to max(u) ≤ 0.8,

M7 ≥ −0.8 corresponds to min(u) ≥ −0.8,

which are needed to impose (5). We uncheck the box which reads 'Make Unconstrained Variables Non-Negative' to allow the variables to take on negative values as well. In the options for the GRG Nonlinear solver, we switch the derivative scheme from the default Forward to Central, and then run the Solver.

Figure 2

Excel Solver reports a feasible solution in less than 3 seconds and generates an Answer Report shown in Figure 3. The optimal switching time is found at approximately 1.26 which is within 1% of the 1.25 value reported by Elnagar and Kazemi [1] using a pseudo-spectral Chebyshev approximation of order 15. The optimal trajectories are plotted in Figure 4.

Figure 3: Answer Report generated by Excel's Solver
Figure 4: optimal trajectories found by Excel Solver.


[1] Elnagar, G.; Kazemi, M.A. Pseudospectral Chebyshev Optimal Control of Constrained Nonlinear Dynamical Systems. Comput. Optim. Appl. 1998, 11, 195-217.

[2] Ghaddar, C. K. "Novel Spreadsheet Direct Method for Optimal Control Problems." Math. Comput. Appl., 23, 6, 2018.
Available at: http://www.mdpi.com/2297-8747/23/1/6

[3] 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.
Available at: https://www.mdpi.com/2297-8747/23/4/54

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