Automatic Differentiation in Microsoft Excel
by
Daniel H. Fylstra
Frontline Systems, Inc.

We report on development of an automatic differentiation facility for the spreadsheet formula language of Microsoft Excel, designed to support our family of solvers and optimizers for spreadsheet models. This facility is a "recalculation engine" for Excel spreadsheets that supports evaluation over reals, intervals, real derivatives and interval derivatives. It also performs "diagnostic" evaluation to assist users in understanding why a given spreadsheet model is linear, smooth nonlinear or non-smooth. We apply the automatic differentiation facility in a variety of ways: Extracting the coefficients of linear programming problems, improving the speed and accuracy of gradient-based classical nonlinear optimizers, providing information to an evolutionary algorithm that exploits smoothness and linearity in subsets of the variables, and supporting the operation of an interval solver for global optimization. We report some empirical results comparing automatic differentiation to finite differencing using the Microsoft-designed recalculation engine in Excel.

Date received: February 15, 2000

Copyright © 2000 by the author(s). The author(s) of this document and the organizers of the conference have granted their consent to include this abstract in Atlas Conferences Inc. Document # cads-81.