O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

8.4. Performing Multiple Linear Regression

Problem

You want to perform multiple linear regression, where an independent variable depends linearly on more than one independent variable.

Solution

Use Excel's LINEST function. LINEST computes statistics for a least-squares straight line through a given set of data. It can be used for linear regression or multiple linear regression. See Recipe 8.3 for an example using LINEST for linear regression. The steps for using LINEST for multiple linear regression are almost identical; the only differences are the model being fit to the data and the number of cells you have to select when entering the array formula.

Discussion

As discussed in Recipe 8.3, the syntax for LINEST is {= LINEST( y-value cell range , x-value cell range , compute intercept , compute statistics )}. Braces surround this formula because it is an array formula. When you type this formula into a cell, you must press Ctrl-Shift-Enter to enter it. Further, you must select a grid of cells before typing and entering the formula. This is because LINEST returns an array of data containing the various statistics computed for the best-fit line. For multiple linear regression, the grid of cells you have to select must be n + 1 columns wide by 5 rows high, where n represents the number of independent variables in the data (the x-variables).

The first argument in LINEST is a cell range containing the y-values for the data to be fit, and the second argument is a cell range containing the

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required