A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet

Abstract

The objective of this present study was to introduce a simple, easily understood method for carrying out non-linear regression analysis based on user input functions. While it is relatively straightforward to fit data with simple functions such as linear or logarithmic functions, fitting data with more complicated non-linear functions is more difficult. Commercial specialist programmes are available that will carry out this analysis, but these programmes are expensive and are not intuitive to learn. An alternative method described here is to use the SOLVER function of the ubiquitous spreadsheet programme Microsoft Excel, which employs an iterative least squares fitting routine to produce the optimal goodness of fit between data and function. The intent of this paper is to lead the reader through an easily understood step-by-step guide to implementing this method, which can be applied to any function in the form y=f(x), and is well suited to fast, reliable analysis of data in all fields of biology.

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.