Calculating Simple Linear Regression - Excel Template

I developed an excel template that generates linear regression analysis. It also writes summary report which is based on correlation coefficient, p-value and beta coefficient. This spreadsheet can handle up to 10,000 cases.

Meaning :In simple linear regression, we predict scores on one variable from the scores on a second variable.The variable we predict is called the dependent or outcome variable and is referred to as Y. The variable we base our predictions on is called the independent or predictor variable and is referred to as X. When there is only one independent or predictor variable, the prediction method is called simple regression.

Examples :

To predict employees' salary on the basis of their years of experience.

To predict students' mathematics aptitude scores based on the number of hours they studied.

Assumptions :

There must be a linear relationship between dependent and independent variable.

Your two variables should be measured at the interval or ratio level.

For any value of X, Y is normally distributed and the variance of Y is the same for all possible values of X.

The Y values are statistically independent of one another.

Regression Equation :

The regression equation is written as :Y = a + bX +e

Y: It is the value of the dependent variable, what is being predicted or explaineda : It is the value of Y when the value of X=0b : How much Y changes for per unit change in X.X: It is the value of the independent variable, what is predicting or explaining the value of Ye: It is the error in predicting the value of Y, given the value of X

Interpretation :

Assume you want to run a regression of wage on work experience.

Regression Equation :Wage = a + b*(years of service)

Suppose the intercept is 1.1 and slope coefficient (b) of years of service is 0.65.

Wage = 1.1 + (0.65) (Years of Service)

An employee having 3 years of experience would be predicted to get wage of 3.05 thousand dollars.

Standard Output of Linear Regression Analysis

1. R and R Square valueThe R value indicates degree of linear relationship between two variables (Simple Correlation). The R square value indicates how much of the dependent variable can be explained by the independent variable.

2. Significance Testing

This is to test whether the regression model predicts the outcome variable significantly well.

Rule :If p value is less than 0.05, that implies the model applied can statistically significantly predict the outcome variable.3. Beta CoefficientIt represents how much dependent variable changes for per unit change in independent variable.

Love this Post? Spread the Word

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.