Nonlinear Parameter Estimation Using Spreadsheet Software

Many fundamental processes in the natural and physical sciences exhibit substantial nonlinear relations among the component variables. Instructing students on how to identify parameters in models for such relations based on measured data, and providing a means to illustrate the impacts of specific components on the behavior of nonlinear relationships, is therefore a critical element of university science training. Commercially available spreadsheet software programs offer convenient and effective means for performing nonlinear parameter estimation. Among the advantages of the spreadsheet approach to nonlinear optimization and parameter estimation are its relative ease of use, general applicability, demonstration or reinforcement of least‐squares principles, and the ability to interactively alter selected parameter values and immediately view the results via embedded graphics. In this paper we provide a brief discussion of nonlinear least‐squares optimization, demonstrate its application within the context of some commercial spreadsheet software packages, and illustrate some of the capabilities of this approach using examples from our respective upper division Soil Physics lecture/laboratory courses. Our experiences indicate that students learn spreadsheet techniques more readily than often specialized mathematical or statistical programs, and that they provide a valuable tool in their future endeavors.