## Recipe 8.2. Constructing Your Own Linear Fit Using Spreadsheet Functions## ProblemYou want to perform a linear curve fit using standard least-squares formulas instead of Excel's linear trendline. ## SolutionUse Excel's built-in formulas such as ## DiscussionThe 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 Excel functions. Figure 8-4 shows a simple spreadsheet I set up for this example. The columns labeled Cell B23 The formula in this cell, `=COUNT(A4:A21)`, computes the number of data points.Cell B24 The formula in this cell, `=SUM(A4:A21)`, computes the sum of x-values.Cell B25 The formula in this cell, `=SUM(B4:B21)`, computes the sum of y-values.Cell B26 The formula in this cell, `=SUMPRODUCT(A4:A21,B4:B21)`, computes the sum of the products on values in the`X`and`Y`columns.Cell B27 The formula in this cell, `=SUMSQ(A4:A21)`, computes the sum of squares of x-values.
## Figure 8-4. Least-squares fitThese cells contain all of the data we need to apply the least-squares formulas. Cell F6 computes the slope of the best-fit line. The formula in cell F6 is The resulting slope and intercept are 0.80869 and 0.007391, respectively. These values agree very well with those obtained using a trendline as discussed in Recipe 8.1. To assess how well this fit equation represents the data, you can compute the R-squared value just as Excel does when it computes chart trendlines. The closer R-squared is to 1, the better the fit. Read Recipe 8.7 to learn more about the R-squared value. For this example, you need to perform a few more calculations in order to compute the R-squared value. Namely, you need to compute the mean of the y-values, the estimated y-values, and the residuals. Figure 8-5 shows these new calculations for this example. Cell F10 computes the mean y-value using Excel's ## Figure 8-5. R-squared computationThe R-squared value is computed in cell F12 by simply taking the ratio of the values in cells I22 and J22. In Figure 8-5, cell F12 is selected and you can see the cell formula for computing R-squared in the formula bar. For this example, R-squared comes out to 0.9985, which indicates a good fit and agrees very well with the value obtained using a chart trendline as discussed in Recipe 8.1. ## See AlsoTake a look at Recipe 8.7 in this chapter to learn more about R-squared. Also, check out the other recipes in this chapter to learn about alternative methods of performing linear curve fitting . |

# Recipe8.2.Constructing Your Own Linear Fit Using Spreadsheet Functions

Similar products

Similar pages