Curve Fitting

Linear Regression Using Microsoft® Excel™

Curve Fitting:

Linear Regression Using Microsoft® Excel™

Introduction

In this exercise, we will assume a basic familiarity with Microsoft® Excel™ spreadsheet software. Some background information about Microsoft Excel is provided at the link to the right. We will briefly present the use of Microsoft Excel to perform linear regression, or fitting a straight line to a set of data. The form of the equation of a straight line is:

\[y = mx + b\]

(1)

In equation (1), x is the abscissa and y is the ordinate, while m is the slope of the line, and b is the y-intercept.

The use of Microsoft Excel to fit a straight line to a set of data will be presented in the context of an example. We will assume that we have measured a set of five data points as follows:

  • (1.3, 2.1), (2.4, 5.2), (3.1, 6.4), (4.2, 7.7), (5.6, 11.2)

In the above set of numbers, the data are presented in pairs of (x,y) points. Steps for using this data to create a straight-line curve fit using Microsoft Excel are presented below.

Input Data

First, we need to enter the data into Microsoft Excel. We will input the x data into the first column and the y data in the second column. Our spreadsheet looks as shown below.

Plot the Data

Select the data in the spreadsheet by clicking and dragging from cell A1 to cell B5. Select the Insert tab and click on Scatter under the chart options and select the option that displays data points, as shown below.

A chart should be added to your spreadsheet, similar to the one shown below.

Create a Straight-line Curve Fit to the Data

Under the Chart Tools, click on the Layout tab. Click on the Trendline button and select More Trendline Options, as shown below.

You should get a dialog box. Choose a Linear trendline and select Display Equation on Chart and Display R-squared value on chart, as shown below. Click on Close.

The equation of the best-fit straight line and the correlation coefficient are added to the chart. Your chart should look approximately as shown below.

The above screenshots are of Microsoft Excel running on Microsoft Windows 7.

  • Other product and company names mentioned herein are trademarks or trade names of their respective companies. © 2014 Digilent Inc. All rights reserved.