How to Calculate Linear Regression Using Excel

1. Type these data pairs starting in cell c3. For this and the remaining steps, press the 'Tab' key where a comma is shown. These numbers are data pairs collected from a hypothetical science experiment. In this experiment, assume there's the possibility of a linear relationship between 'x,' the first column of numbers, and 'y,' the second column.X, Y1, 5.22, 7.83, 10.74, 13.95, 16.5
2. Type these additional three columns, starting with the first cell to the right of the cell containing 'y.' These columns are factors in the calculations for the slope, y-intercept and R values of linear equations of the form y = mx b. Letter 'm' is the slope, 'b' is the y-intercept and 'R' is a measure of how closely the computed line matches the actual data points. The closer 'R' is to 1.0, the closer the data points are to forming an actual line whose 'm' and 'b' values are those you're computing.xy, x^2, y^2c4*d4, c4*c4, d4*d4
3. Select the second row you just typed, then click the lower-right corner of the rightmost cell. Drag downward until the selection is five rows high. This action extends the formulas to all the x-y data pairs.
4. Type these additional six cells starting at cell b11. These cells contain summations of the columns you entered in the previous step.n, sum of x, sum of y, sum of (xy), sum of (x^2), sum of (y^2)count(c4:c9), sum(c4:c9), sum (d4:d9), sum (e4:e9), sum (f4:f9), sum(g4:g9)
5. Type these formulas starting in cell c14. These are squares of two of the summation calculations you entered in the previous step.(sum of x)^2, (sum of y)^2c12^2, d12^2
6. Type these labels and calculations starting in cell c17. These are the slope, y-intercept and 'R' values of the estimated line, as described in step 2. After entering these final calculations, look first at the 'R' value, 0.9994. This number is close to 1.0, which means the line you calculated is close to fitting the data points. Next, compare how close the slope, 2.87, is with the value 3.0, which is the slope of the actual line used to create data points for this article. Last, relate the y-intercept value, 2.21, to the value 2.0, which is the y-intercept of the linear equation used to create data points for this article.slope, (B12*E12-C12*D12)/(B12*F12-C15)y-intercept, (D12-D17*C12)/B12R, (B12*E12-C12*D12)/SQRT((B12*F12-C15)*(B12*G12-D15))