Unconventional Spreadsheet Functions

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