Thursday, May 29, 2014

All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013

This is one of the following seven articles on Simple Linear Regression in Excel

Overview of Simple Linear Regression in Excel 2010 and Excel 2013

Complete Simple Linear Regression Example in 7 Steps in Excel 2010 and Excel 2013

Residual Evaluation For Simple Regression in 8 Steps in Excel 2010 and Excel 2013

Residual Normality Tests in Excel – Kolmogorov-Smirnov Test, Anderson-Darling Test, and Shapiro-Wilk Test For Simple Linear Regression

Evaluation of Simple Regression Output For Excel 2010 and Excel 2013

All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013

Prediction Interval of Simple Regression in Excel 2010 and Excel 2013

 

All Excel Calculations For

Simple Regression

Performing regression analysis manually can be done but is somewhat tedious. Remember also that single-variable regression performed here is the simplest type of regression. If a few more independent variables, the calculations become exponentially more complicated. All of the calculations needed to duplicate Excel’s regression output are as follows:

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,stndard error,regression equation (Click On Image To See a Larger Version)

 

Calculation of Coefficient

and Intercept in Excel

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation (Click On Image To See a Larger Version)

 

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation (Click On Image To See a Larger Version)

 

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation (Click On Image To See a Larger Version)

 

Calculation of R Square in Excel

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation
(Click On Image To See a Larger Version)

 

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation
(Click On Image To See a Larger Version)

 

Another Way To Calculate R Square in Excel

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation
(Click On Image To See a Larger Version)

 

Calculation of Adjusted R Square

in Excel

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation
(Click On Image To See a Larger Version)

 

Calculation of the Standard Error

of the Regression Equation in Excel

The Standard Error of the Regression Equation is calculated from the residuals.

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square,standard error,regression equation (Click On Image To See a Larger Version)

 

ANOVA Calculations in Excel

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

The p Value formula above is the legacy formula for Excel versions prior to 2010. Excel 2010 and later use the following p Value formula:

Significance of F = p Value = F.DIST.RT(F Stat, 1, n – 2)

The F Statistic is the result of an F Test that calculates the ratio of the Explained variance over the Unexplained variance. If this ratio is large enough, it is unlikely that this result was obtained by chance.

Significance of F is a p Value that determines the overall validity of the regression equation. If the p Value is smaller than the designated Alpha (Level of Significance), then it can be said that the regression equation is significant at the designated Level of Confidence (Level of Confidence = 1 – Level of Significance).

It is the p Value derived from the F Test that produced the F Statistic. This p Value (the percentage of the total area under the F distribution curve beyond the F Statistic) provides the probability that the regression equation was arrived at merely by chance.

 

Analysis of the Independent

Variable Coefficient in Excel

The overall test being conducted on the Variable Coefficient is a t-Test that has a Null Hypothesis stating that this regression variable = 0. This Null Hypothesis will be rejected if the t Statistics of this Regression Variable is large enough or, equivalently, the p-Value associated with that t Statistic is small enough.

 

Standard Error of Coefficient Calculation in Excel

One of the first steps in a hypothesis test is to determine the standard error of the distributed variable.

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

t Stat of Coefficient Calculation in Excel

The t Statistic of the coefficient states how many standard errors that the coefficient is from zero.

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

p-Value of the Coefficient calculation in Excel

The p-value of the coefficient is calculated from the t Statistic. The smaller the t-Statistic is, the larger will be the p Value. The very small p Value in this case indicates that validity of the calculated value of coefficient. This p Value of approximately zero indicates that there is almost no possibility that this calculated value of the coefficient occurred merely by chance.

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

95% Confidence Interval of Coefficient Calculation in Excel

This interval has a 95% chance of containing the coefficient

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

Analysis of Intercept

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation
(Click On Image To See a Larger Version)

The overall test being conducted on the Y Intercept is a t-Test that has a Null Hypothesis stating that the intercept = 0. This Null Hypothesis will be rejected if the t Statistics of this Intercept is large enough or, equivalently, the p-Value associated with that t Statistic is small enough.

 

Standard Error of the Intercept Calculation in Excel

One of the first steps in a hypothesis test is to determine the standard error of the intercept.

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

t Stat of the Intercept Calculation in Excel

The t Statistic of the Intercept states how many standard errors that the Intercept is from zero.

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

p-Value of the Intercept Calculation in Excel

The p-value of the Intercept is calculated from the t Statistic. The smaller the t-Statistic is, the larger will be the p Value. The small p Value in this case indicates that validity of the calculated value of Intercept. This p Value of 0.017 indicates that there is only a 1.7 percent that this calculated value of the Intercept occurred merely by chance.

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

95% Confidence Interval of Intercept Calculation in Excel

This interval has a 95% chance of containing the Intercept

regression, excel, excel 2010, excel 2013, statistics, r square, adjusted r square, standard error, regression equation (Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

No comments:

Post a Comment