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.3. Using a Single Spreadsheet Function for Linear Curve Fitting

Problem

You want to perform a quick linear curve fit without using chart trendlines and without having to write the least-squares formulas yourself.

Solution

Use Excel's LINEST function.

Discussion

LINEST computes statistics for a least-squares straight line through a given set of data. The syntax for LINEST is {= LINEST( y-value cell range , x-value cell range , compute intercept , compute statistics )}. Note the braces surrounding this formula since it is an array formula. When you type this formula into a cell, you have to press Ctrl-Shift-Enter to enter it. Further, you have to select a 2 × 5 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.

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 x-values. The third argument is a logical value (true or false) specifying whether or not to force the intercept of the fit line to pass through zero. If true, the intercept is calculated in the usual least-squares manner. If false, the intercept is forced to zero, with the slope computed accordingly. The fourth argument is a logical value indicating whether or not to display extended statistics for the best-fit line. These extended statistics include such things as standard errors and residual sums. (See the "LINEST" ...

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