A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet
Introduction
The use of curve fitting to describe experimental data is widespread in all fields of biology. The purpose of such analysis is to standardize data interpretation into a uniformly recognized form. Curve fitting essentially describes the experimental data as a mathematical equation in the form y=f(x), where x is the ‘independent’ variable and is controlled by the experimenter; y is the ‘dependent’ variable, which is measured; and f is the function, which includes one or more parameters used to describe the data. The better the fit, the more accurately the function describes the data. The introduction of personal computers into laboratories has greatly reduced the time and effort required in analyzing data and it is a relatively straightforward process to fit data with simple functions such a linear regression, a process that can be carried out with a few simple point-and-click commands. It is more difficult, however, to fit data with more complicated non-linear functions. This is usually carried out by specialist programmes such as Microcal Origin, Sigma Plot or Graphpad Prism. An advantage of these programmes is that they are capable of fitting user-input functions to data. However these programmes tend to be expensive (in the £500 range), and if the goal is simply to fit data with a non-linear function, the user pays for a vast excess of redundant features. These programmes are aimed at experienced specialist users with a mathematical background and tend to be difficult for the novice to learn. Additionally, these programmes do not handle data manipulation well and tend to display data, graphs, results, and analysis in a multitude of separate windows, which can lead to confusion.
An alternative method is to use Microsoft Excel to fit non-linear functions. An advantage of this method is that Excel is probably included in the computer package as part of Microsoft Office, and thus no additional expense is required. Spreadsheet programmes are among the most commonly used software, and most biologists have experience with them even if at an elementary level. Excel offers a friendly user interface, flexible data manipulation, built-in mathematical functions and instantaneous graphing of data. Excel contains the SOLVER function, which is ideally suited to fitting data with non-linear functions via an iterative algorithm [1], which minimizes the sum of the squared difference between data points and the function describing the data. The objective of this present study was to describe a method of non-linear regression using the SOLVER function of Excel.
Section snippets
Method
The method described in this paper, to conduct a curve fitting protocol in an Excel spreadsheet, was carried out on a Gateway Pentium II computer running Microsoft Windows 98 and Excel 97. The protocol involves entering data manually into the spreadsheet and graphing the data. Once the data have been entered, the curve fitting protocol is carried out and the curve fit is overlaid on the data points. Goodness of fit data are also calculated so that the accuracy of fit can be assessed.
Conclusion
Non-linear regression is a powerful technique for standardizing data analysis. The advent of personal computers has rendered linear transformation of data obsolete, allowing non-linear regression to be carried out quickly and reliably by non-specialist users. While the method described in this paper requires that the user have a basic knowledge of spreadsheets, it is not required that the user has an intimate understanding of the mathematics behind the processes involved in curve fitting. This
References (6)
- et al.
Nonlinear regression using spreadsheets
TiPS
(1995) Why, when, and how biochemists should use least squares
Anal Biochem.
(1992)- et al.
Design and testing of a generalized reduced gradient code for nonlinear programming
ACM Trans. Mathematical Software
(1978)
Cited by (394)
Evaluation of full-energy-peak efficiencies for a LaBr<inf>3</inf>:Ce scintillator through a Virtual Point Detector approach
2024, Radiation Physics and ChemistryThermal performance study of buildings and their corresponding scale models with identical constructions and geometrical similarity
2024, Journal of Engineering Research (Kuwait)Development of sustainable concrete using recycled polyethylene terephthalate (PET) granules as fine aggregate
2023, Developments in the Built EnvironmentBiomethane potential of wine lees from mesophilic anaerobic digestion
2023, Biochemical Engineering Journal