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.2. Constructing Your Own Linear Fit Using Spreadsheet Functions

Problem

You want to perform a linear curve fit using standard least-squares formulas instead of Excel's linear trendline.

Solution

Use Excel's built-in formulas such as COUNT, SUM, SUMSQ, and SUMPRODUCT to make it easy to apply the standard least-squares formulas.

Discussion

The standard equation for a straight line is:

The standard least-squares formulas used to determine the slope, m, and intercept, b, of the fit line are:

In these equations, n is the number of data points. Excel has several built-in functions that make it very easy to compute the various sums that appear in the least-squares equations. These functions include:

COUNT

This function counts the number of cells containing numbers in a range of cells.

SUM

This function adds all the numbers in a range of cells.

SUMSQ

This function returns the sum of squares of the numbers contained in a range of cells.

SUMPRODUCT

This function sums the products of entries in corresponding ranges of cells.

Let's reconsider the data used in the example in Recipe 8.1. Instead of using a chart trendline to determine the best-fit line, we'll use the least-squares equations and built-in ...

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