Excel Scientific and Engineering Cookbook

Chapter 8. Curve Fitting and Regression

Introduction

A very common exercise when analyzing data is fitting a curve through that data. Curve fitting is the process of trying to find the curve (which is represented by some model equation) that best represents the sample data, or more specifically the relationship between the independent and dependent variables in the dataset. When the results of the curve fit are to be used for making new predictions of the dependent variable, this process is known as regression. Sometimes, you may want to fit a curve just to interpolate a set of data. At other times, you may actually want to extrapolate beyond the sample data. In still other cases, curve fitting can be used to predict parameters of some known model (an equation) given a set of observed data. In this chapter, I discuss several different techniques for performing linear and nonlinear curve fitting in Excel.

8.1. Performing Linear Curve Fitting Using Excel Charts

Problem

You’d like to generate a best-fit straight line for a set of data.

Solution

Use Excel’s chart trendline feature to perform a linear curve fit of your data. Plot your data using an XY scatter chart (see Chapter 4). Once your chart has been created, right-click on the data series and select Add Trendline from the pop-up menu.

Discussion

Linear curve fits are easily generated using the trendline feature built into Excel’s XY scatter chart. Once you’ve plotted your data using an XY scatter chart, you can generate a trendline ...