Now, click OK and Excel will perform the linear regression, and put the output on a new page:Ĭell B17 contains the “intercept” i.e. Since we have labels at the top of each data column (and included their cells in the ranges above) click the “Labels” checkbox and then click the “Line Fit Plots” checkbox. Next, click inside the box labelled “Input X Range:” and then click on cell C1 and hold the left mouse button down and highlight cells C1 through C14 Scroll down until you see the “Regression” tool, click on it, then click OK.Ĭlick inside the box labeled “Input Y Range:”Ĭlick on cell B1 and hold the left mouse button down and highlight cells B1 throught B14 For ease of computation, it helps to put the dependent variable (Y) in the left column, and the independent variable (X) in the right column.Ĭlick on “Data” and then “Data Analysis” and a window like this will appear. You start by collecting a list observations or data, and recording them in your spreadsheet. due to other factors (age, years on job, etc.) error i = portion of Y (salary) that is unrelated to X (education), i.e.X i = value of X (education) for observation i.

the average increase in salary for each additional year of education b = average change in Y (salary) given a one unit increase in X (education), i.e.a = average value of Y (salary) when X (education) is zero.Y i = value of Y (salary) for observation i.Suppose you want to determine whether a person’s salary is a function of his or her education level (measured in years). Output options specify how you would like the results to be displayed, Residuals contains options to draw the results as charts.A linear regression is just a statistical tool used to determine whether or not two (or more) variables are linearly related.īefore you can perform a linear regression with Excel, you need to make sure the “Analysis ToolPak” is installed.

Lets review the main elements in the above mentioned dialogue box, Input Y Range option lets you specify the dependent variables and the Input X Range option lets you specify the independent variables. It will launch the dialogue box which will let you apply the regression feature on your data. Once Data Analysis is enabled, open it and choose the Regression option and hit Enter. The Regression function is available under the Data Analysis feature of Microsoft Excel. In layman’s term, it lets you see how value of one variable(also called dependent variable) changes when any one of the independent variables varies.Īccessing Regression Analysis In Microsoft Excel 2010 Regression analysis is a pure statistical term which includes the techniques for modeling and analyzing several variables, when the focus is on the relationship between a dependent variable and one or more independent variables.

Data regression excel 2010