You want to perform a linear curve fit using standard least-squares formulas instead of Excel's linear trendline.
Use Excel's built-in formulas such as
SUMPRODUCT to make it easy to apply the standard least-squares formulas.
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:
This function counts the number of cells containing numbers in a range of cells.
This function adds all the numbers in a range of cells.
This function returns the sum of squares of the numbers contained in a range of cells.
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 ...