Modeling and Optimization of Dynamical Systems by Unconventional Spreadsheet Functions

Chahid Kamel Ghaddar^1,

¹ExcelWorks LLC, Sharon, MA,USA

Cite this paper:
Chahid Kamel Ghaddar. Modeling and Optimization of Dynamical Systems by Unconventional Spreadsheet Functions. American Journal of Modeling and Optimization. 2016; 4(1):1-12. doi: 10.12691/ajmo-4-1-1

Abstract

The spreadsheet computational engine is exploited via a nonstandard mechanism to support a functional formulation for constrained optimization of parameterized differential systems by unconventional spreadsheet functions. The nonstandard mechanism enables encapsulation of numerical algorithms into functions which take variable formulas as a new type of input argument while retaining purity and recursion properties. This is in contrast to conventional spreadsheet functions which are restricted to static input types. Several solvers for differential equations and nonlinear minimization are developed which serve as building blocks for the functional formulation. The latter makes it possible to express a program for a constrained dynamical minimization problem in as few as three formula evaluations in Excel as demonstrated by several examples. The solver functions integrate seamlessly with MS Excel, and propel the spreadsheet beyond traditional applications as a powerful tool for exploring dynamical optimization problems.

Keywords:
dynamical optimization optimal control differential equations spreadsheet functional paradigm

This work is licensed under a Creative Commons Attribution 4.0 International License. To view a copy of this license, visit http://creativecommons.org/licenses/by/4.0/

References:

[1]	Larsen, R. W., “Engineering with Excel,” Pearson PrenticeHall 2009, New Jersey.
[2]	Bourq, David M., “Excel scientific and engineering cookbook,” O’Reilly, 2006.
[3]	Laughbaum, Edward D., Seidel, Ken, “Business math Excel applications,” Prentice Hall ; 2008.
[4]	E. J. Billo, Excel for Scientists and Engineers, WILEY-INTERSCIENCE, 2007.
[5]	Ali El-Hajj, Sami Karaki, Mohammed Al-HusseiniKarim Y. Kabalan, “Spreadsheet Solution of Systems of Nonlinear Differential Equations”, Spreadsheets in Education, Vol 1, Issue 3.
[6]	M. B. Cutlip and M. Shacham, Problem Solving in Chemical and Biochemical Engineering with POLYMATH, Excel and MATLAB,Prentice Hall, 2008.
[7]	Chung-Yau Lam and F. H. Alan Koh, “A Partial Differential Equation Solver for the Classroom,” Int. J. Engng Ed.Vol. 22, No. 4, pp. 868-875, 2006.
[8]	Hagler, Marion, “Spreadsheet Solution of Partial Differential Equations,” IEEE Transactions on Education, Volume:E-30 Issue:3.
[9]	Olsthoorn TN (1998) Groundwater modelling: calibration and the use of spreadsheets. Delft University Press, Delft, ISBN 90-407-1702-8, CIP, about 300 pp.
[10]	Karahan H. (2007). Unconditional stable explicit finite difference technique for the advection-diffusion equation using spreadsheets. Adv.EngSoftw 38(2):80-86.
[11]	Palisade Corporation, “Evolver. The Genetic Algorithm Super Solver for Microsoft Excel.”, Palisade Corporation (2001). https://www.palisade.com/evolver/.
[12]	Cliff Ragsdale, “Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Management Science, 6th Edition”. College Bookstore, 2011.
[13]	S. Dalton, Financial Applications using Excel Add-in Development in C/C++ , The Wiley Finance Series, 2007.
[14]	Excel Commands, Functions, and States, MSDN publication, https://msdn.microsoft.com/en-us/library/bb687832(v=office.15).aspx.
[15]	Description of limitations of custom functions in Excel. https://support.microsoft.com/en-us/kb/170787.
[16]	The MathworksInc, MATLAB Builder EX, http://www.mathworks.com/products/matlabxl/.
[17]	C. Ghaddar, “Method, Apparatus, and Computer Program Product for Optimizing Parameterized Models Using Functional Paradigm of Spreadsheet Software,” USA Patent No. 9286286.
[18]	C. Ghaddar, Unconventional Calculus Spreadsheet Functions, ICMS 2016: 18th International Conference on Mathematics and Statistics. Boston.
[19]	R. Piessens, E. de Doncker-Kapenga, C.W. Ueberhuber, and D.K. Kahaner, QUADPACK A subroutine package for automatic integration, Springer Verlag, 1983.
[20]	Wikipedia. Functional Programming. https://en.wikipedia.org/wiki/Functional_programming
[21]	K. Levenberg, A Method for the Solution of Certain Non-Linear Problems in Least Squares, Quarterly of Applied Mathematics vol2, 164-168, 1944.
[22]	D. Marquardt , An Algorithm for Least-Squares Estimation of Nonlinear Parameters,SIAM Journal on Applied Mathematics vol11 (2), 431-441, 1963.
[23]	V. Arnăutu and P. Neittaanmäki, “Optimal Control from Theory to Computer Programs” Springer. 2003.
[24]	C. Ghaddar, ExceLab Reference Manual, www.excel-works.com
[25]	E Hairer and G Wanner, Solving Ordinary Differential Equations II: Stiff and Differential-Algebraic Problems, Springer Series in Computational Mathematics, 1996.
[26]	Alan C. Hindmarsh, ODEPACK, A Systematized Collection of ODE Solvers, in Scientific Computing, R. S. Stepleman et al. (Eds.), North-Holland, Amsterdam, 1983, pp. 55-64.
[27]	U. M. Ascher, R. M. Mattheij and R. D. Russell, Numerical Solution of Boundary Value Problems for Ordinary Differential Equations, SIAM, 1995.
[28]	U. Ascher and R. Spiteri, Collocation software for boundary value differential-algebraic equations, SIAM Journal on Scientific Computing. 1994, 15,938-952.
[29]	GAWAIN, T.H., AND BALL, R.E. Improved finite difference formulas for boundary value problems. Int. J. Numer. Meth. Eng. 12 (1978), 1151-1160.
[30]	Schiesser W.E (1991).The Numerical Method of Lines, San Diego, CA: Academic Press, 1991.
[31]	Gao, Zhang and Cao in the article: “Differentiation and numerical Integral of the Cubic Spline Interpolation”, in the Journal of Computers, Vol. 6, No 10, 2011.