Unconventional Calculus Spreadsheet Functions

Authors: Chahid K. Ghaddar

Abstract:

The spreadsheet engine is exploited via a non-conventional mechanism to enable novel worksheet solver functions for computational calculus. The solver functions bypass inherent restrictions on built-in math and user defined functions by taking variable formulas as a new type of argument while retaining purity and recursion properties. The enabling mechanism permits integration of numerical algorithms into worksheet functions for solving virtually any computational problem that can be modelled by formulas and variables. Several examples are presented for computing integrals, derivatives, and systems of deferential-algebraic equations. Incorporation of the worksheet solver functions with the ubiquitous spreadsheet extend the utility of the latter as a powerful tool for computational mathematics.

Keywords: Calculus functions, nonlinear systems, differential algebraic equations, solvers, spreadsheet.

Digital Object Identifier (DOI): doi.org/10.5281/zenodo.1124193

References:

[1] Laughbaum, Edward D., Seidel, Ken, “Business math Excel applications,” Prentice Hall 2008.
[2] Larsen, R. W., “Engineering with Excel,” Pearson Prentice Hall 2009, New Jersey. ISBN 0-13-601775-4
[3] Bourq, David M., “Excel scientific and engineering cookbook,” O’Reilly, 2006
[4] E. J. Billo, Excel for Scientists and Engineers, WILEY-INTERSCIENCE, 2007
[5] Kim Gaik Tay, Tau Han Cheong, Nur Kamil Adli Mohd Nawar, Sie Long Kek, Rosmila Abdul-KaharA, “Romberg Integral Spreadsheet Calculator”, Spreadsheets in Education (eJSiE), 2015
[6] Excel Commands, Functions, and States, MSDN publication, accessed 1/20/2016, https://msdn.microsoft.com/en-us/library/bb687832(v=office.15).aspx
[7] S. Dalton, Financial Applications using Excel Add-in Development in C/C++ , The Wiley Finance Series, 2007.
[8] Description of limitations of custom functions in Excel, accessed 1/20/2016, https://support.microsoft.com/en-us/kb/170787
[9] C. Ghaddar, “Modeling and Optimization of Dynamical Systems by Unconventional Spreadsheet Functions.” American Journal of Modeling and Optimization. Vol. 4, No. 1, 2016.
[10] C. Ghaddar, “Method, Apparatus, and Computer Program Product for Optimizing Parameterized Models Using Functional Paradigm of Spreadsheet Software,” USA Patent No. 9286286.
[11] R. Piessens, E. de Doncker-Kapenga, C.W. Ueberhuber, and D.K. Kahaner, “QUADPACK A subroutine package for automatic integration,” Springer Verlag, 1983.
[12] C. Ghaddar, “ExceLab Reference Manual”, accessed 3/7/2016, www.excel-works.com
[13] C.J.F. Ridders, Advances in Engineering Software, vol 4, 75-76, 1982.
[14] Numerical Recipes in C: The Art of Scientific Computing, Cambridge University Press, 1992.
[15] K. Levenberg, “A Method for the Solution of Certain Non-Linear Problems in Least Squares,” Quarterly of Applied Mathematics vol 2, 164–168, 1944.
[16] D. Marquardt “An Algorithm for Least-Squares Estimation of Nonlinear Parameters,” SIAM Journal on Applied Mathematics vol 11 (2), 431–441, 1963.
[17] J. More, B. S. Garbow, and K. E. Hillstrom, “Testing unconstrained optimization software,” ACM Trans. Math. Softw, vol 7, 17-41, 1981
[18] E Hairer and G Wanner, “Solving Ordinary Differential Equations II: Stiff and Differential-Algebraic Problems,” Springer Series in Computational Mathematics, 1996.
[19] A. 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.
[20] U. M. Ascher, R. M. Mattheij and R. D. Russell, “Numerical Solution of Boundary Value Problems for Ordinary Differential Equations,” SIAM, 1995.
[21] U. Ascher and R. Spiteri “Collocation software for boundary value differential-algebraic equations,” SIAM Journal on Scientific Computing. 1994, 15,938-952.
[22] K Soetaert, J. Cash, and F. Mazzia, Package bvpSolve, solving test problems, accessed 1/20/2016, http://www.ma.ic.ac.uk/~jcash/BVP_software/PROBLEMS.PDF