## 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: (Click On Image To See a Larger Version)

## Calculation of Coefficient and Intercept in Excel (Click On Image To See a Larger Version) (Click On Image To See a Larger Version) (Click On Image To See a Larger Version)

## Calculation of R Square in Excel (Click On Image To See a Larger Version) (Click On Image To See a Larger Version)

### Another Way To Calculate R Square in Excel (Click On Image To See a Larger Version)

## Calculation of Adjusted R Square in Excel (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. (Click On Image To See a Larger Version)

## ANOVA Calculations in Excel (Click On Image To See a Larger Version) (Click On Image To See a Larger Version) (Click On Image To See a Larger Version) (Click On Image To See a Larger Version) (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. (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. (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. (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 (Click On Image To See a Larger Version)

## Analysis of Intercept (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. (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. (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. (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 (Click On Image To See a Larger Version)

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

• Histograms in Excel
• Bar Chart in Excel
• Combinations & Permutations in Excel
• Normal Distribution in Excel
• t-Distribution in Excel
• Binomial Distribution in Excel
• z-Tests in Excel
• t-Tests in Excel
• Hypothesis Tests of Proportion in Excel
• Chi-Square Independence Tests in Excel
• Chi-Square Goodness-Of-Fit Tests in Excel
• F Tests in Excel
• Correlation in Excel
• Pearson Correlation in Excel
• Spearman Correlation in Excel
• Confidence Intervals in Excel
• Simple Linear Regression in Excel
• Multiple Linear Regression in Excel
• Logistic Regression in Excel
• Single-Factor ANOVA in Excel
• Two-Factor ANOVA With Replication in Excel
• Two-Factor ANOVA Without Replication in Excel
• Randomized Block Design ANOVA in Excel
• Repeated-Measures ANOVA in Excel
• ANCOVA in Excel
• Normality Testing in Excel
• Nonparametric Testing in Excel
• Post Hoc Testing in Excel
• Creating Interactive Graphs of Statistical Distributions in Excel
• Solving Problems With Other Distributions in Excel
• Optimization With Excel Solver
• Chi-Square Population Variance Test in Excel
• Analyzing Data With Pivot Tables
• SEO Functions in Excel
• Time Series Analysis in Excel
• VLOOKUP