Minimal Swing Container Transfer Problem

The third example represents the problem of transferring containers, driven by a hoist motor and a trolley drive motor, from a ship to a cargo truck. The goal is to minimize the swing during and at the end of the transfer. The mathematical optimal control problem is described by (1)-(13). The problem is nonlinear with six state variables and two controllers subject to multiple final and bound constraints.

Minimize

(1)

subject to

(2)

(3)

(4)

(5)

(6)

(7)

Initial conditions:

(8)

Final conditions:

(9)

Bounds:

(10)

(11)

(12)

(13)

Spreadsheet Model

Following the same procedure as that in the previous examples, we define the model for the IVP (2)-(8) using third-order parametrized polynomial control functions as shown in Table 1. Initial values and guesses are assigned to the state variables and unknown parametrization coefficients as shown in the table.

Table 1
ABCD
1 ODE variables
2t0
3x_10
4x_222
5x_30
6x_40
7x_5-1
8x_60
9 Parametrized controls formulas
10c_01d_01
11c_11d_11
12c_2-5d_2-5
13c_3-5d_3-5
14u_1=c_0+c_1*t+c_2*t^2+c_3*t^3
15u_2=d_0+d_1*t+d_2*t^2+d_3*t^3
16 ODE rhs equations
17x1dot=9*x_4
18x2dot=9*x_5
19x3dot=9*x_6
20x4dot=9*(u_1+x_3)
21x5dot=9*u_2
22x6dot=9*(u_1+27.0756*x_3+2*x_5*x_6)/x_2

Table 2 shows a partial listing of the initial solution obtained by evaluating the array formula =IVSOLVE(B17:B22, B2:B8, {0,1}) in array F2:L103, along with the generated control columns for u_1, u_2, and the integrand expression using the corresponding formulas listed in Table 3. The initial system trajectories are plotted in Figure 1.

Next, we defined the objective formula, S3, corresponding to the cost index (17) as shown in Table 3, in which the data integrator QUADXY was used to integrate the generated integrand expression values. The objective formula evaluated to an initial value of 24,229.22793. Table 3 also lists a number of aid formulas which compute the minimum and maximum values for the state variables x_4 and x_5 and the generated control columns. These aid formulas were used to define the bound constraints for the NLP Solver.

Table 2
FGHIJKLNOPRS
1IVP Solution
2tx_1x_2x_3x_4x_5x_6u_1 u_2 Integrand
3002200-10110Objective24229.22793
40.010.00406321.914060.0001850.09044-0.909570.004111.0094951.0094951.69E-05
50.020.01630521.83630.0007420.181723-0.818320.0082851.017961.017966.92E-05Constraints
60.030.03679721.766790.0016790.273786-0.726360.0125691.0253651.0253650.000161max(u1)1.045855
1010.98230.673115.34798189.5472205.5287-12.3527147.8948-7.52796-7.5279657801.01
1020.99249.926714.20542203.2503222.511-13.0407156.6717-7.762-7.76265856.72
1031270.763413217.7568240.7409-13.75165.7417-8-874888.35
Table 3: formulas used to generate values shown in Table 2
PurposeCellFormula
Initial value problem solutionF2:L103=IVSOLVE(B17:B22, B2:B8, {0,1})
AutoFill formula for u_1 control valuesN3=c_0+c_1*F3+c_2*F3^2+c_3*F3^3
AutoFill formula for u_2 control valuesO3=d_0+d_1*F3+d_2*F3^2+d_3*F3^3
AutoFill formula for integrand valuesP3=I3^2+L3^2
Objective FormulaS3=4.5*QUADXY(F3:F103, P3:P103)
u_1 column max valueS6=MAXA(N3:N103)
u_1 column min valueS7=MINA(N3:N103)
u_2 column max valueS8=MAXA(O3:O103)
u_2 column min valueS9=MINA(O3:O103)
x_4 column max valueS10=MAXA(J3:J103)
x_4 column min valueS11=MINA(J3:J103)
x_5 column max valueS12=MAXA(K3:K103)
x_5 column min valueS13=MINA(K3:K103)
Figure 1: initial trajectories

Results and Discussion

We configured Excel Solver to minimize the objective formula S3 by varying the controls' coefficients B10:B14 and D10:D14 subject to the constraints listed in Table 4. The Solver spun for a few seconds, then reported that it did not find a feasible solution when, in fact, it already had, judging by the best-found solution results shown partially in Figure 2 and plotted in Figure 3. The solution indicates that all constraints were satisfied within a reasonable tolerance of 1x10-5, except for x_6(1), which was satisfied within a tolerance of 1x10-3. This is verified by the feasibility report generated by the Solver, and shown in Figure 4.

Table 4. constraints added to Excel Solver
FormulaConstraint
G103 = 10
H103 = 14
I103 = 0
J103 = 2.5
K103 = 0
L103 = 2.5
S10 ≤ 2.5(12)
S11 ≥ -2.5
S12 ≤ 1(13)
S13 ≥ -1
S6 ≤ 2.83374(10)
S7 ≥ -2.83374
S8 ≤ 0.71265(11)
S9 ≥ -0.80865
Figure 2: best-found solution obtained by Excel Solver
Figure 3: optimal trajectories
Figure 4: Feasibility Report generated by Excel Solver

References

[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