How to Find the Regression Equation in Excel 2007

From Graphing
1. Select all the x (independent variable) and y (dependent variable) data.
2. Click on 'Insert' in the top menu bar, then on 'Scatter.' Click on any of the five chart options.
3. Click once on the graphed line. Right-click the line and choose 'Add Trendline...' from the menu that appears.
4. Click the check-box for 'Display Equation on chart' at the bottom of the window that appears. Click 'Close.' The regression equation for the data appears on the chart. With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the equation that appears is 'y = 2.4*x 7.2.'
Built-in Excel Functions
5. Find the slope of the regression equation, 'm,' by typing in any empty cell '=slope(known_ys, known_xs).' For example, if the known y-values are the cell range B1:B5 and the known x-values are in the cell range A1:A5, then type '=slope(B1:B5,A1:A5).' With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the result is a slope of two and four tenths.
6. Find the y-intercept of the regression equation, 'b,' by typing in any empty cell '=intercept(known_ys, known_xs).' For example, if the known y-values are the range B1:B5 and the known x-values are in the range A1:A5, then type '=intercept(B1:B5,A1:A5).' With the same examples values as before, the result is a y-intercept of seven and two tenths.
7. Write out the regression equation now as 'y = m*x b.' In our example, the regression line equation is 'y = 2.4*x 7.2.'
Brute Force Calculations
8. Calculate the sum of all the x-values, denoted (x). Do this by typing in an empty cell '=sum(A1:A5),' where A1:A5 is the range of x-values. Perform the same operation to find the sum of all the y-values, denoted (y), which may for example be in the range B1:B5.
9. Calculate the sum of the product of each x and y pair, denoted (xy). Do this by summing together A1*B1, A2*B2, etc.. in the same fashion as step one. Also, denote the number of x-y pairs as 'n.'
10. Calculate the sum of the square of each x-value, denoted (x^2). Do this by summing together A1^2, A2^2, etc.. in the same fashion as step one.
11. Calculate the slope, 'm,' of the regression equation by performing the following calculation: n(xy) - (x)*(y). Then calculate n(x^2) - (x)^2. Lastly, divide the first result by the second result. With example x-values (1,2,3,4,5) and y-values (10,12,14,16,20), the result is a slope of two and four tenths.
12. Calculate the y-intercept, 'b,' of the regression equation by performing the following calculation: (y)-m*(x). Lastly, divide the result by 'n.' With the same example data as the previous step, the result is a y-intercept of seven and two tenths.