## Thursday, May 29, 2014

### Prediction Interval of Simple Regression 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

# Prediction Interval of  Simple Regression in Excel

A prediction interval is a confidence interval about a Y value that is estimated from a regression equation. A regression prediction interval is a value range above and below the Y estimate calculated by the regression equation that would contain the actual value of a sample with, for example, 95 percent certainty.

The Prediction Error for a point estimate of Y is always slightly larger than the Standard Error of the Regression Equation shown in the Excel regression output directly under Adjusted R Square.

The Standard Error of the Regression Equation is used to calculate a confidence interval about the mean Y value. The Prediction Error is use to create a confidence interval about a predicted Y value. There will always be slightly more uncertainty in predicting an individual Y value than in estimating the mean Y value.

For that reason, a Prediction Interval will always be larger than a Confidence Interval for any type of regression analysis.

Calculating an exact prediction interval for any regression with more than one independent variable (multiple regression) involves some pretty heavy-duty matrix algebra. Fortunately a prediction interval for simple regression can be calculated by hand as follows:

## Prediction Interval Formula For Simple Regression

The formula for a prediction interval about an estimated Y value (a Y value calculated from the regression equation) is found by the following formula:

Prediction Interval = Yest ± t-Valueα/2,df=n-2 * Prediction Error

Prediction Error = Standard Error of the Regression * SQRT(1 + distance value)

Distance value, sometimes called leverage value, is the measure of distance of the combinations of values, x1, x2,…, xk from the center of the observed data. Distance value in any type of multiple regression requires some heavy-duty matrix algebra. This is given in Bowerman and O’Connell (1990).

Distance value can be calculated for single-variable regression in a fairly straightforward manner as follows:

Distance value = 1/n + [(x0 – x_bar)2]/SSxx

If, for example we wanted to calculate the 95 percent Prediction Interval for the estimated Y value when X = 5000 kg. of input pellets, the following calculations would be performed:

x0 = 5,000

n = 20

Yest = Number of Parts Produced = 1,345.09 + 1.875 (Weight of Input Pellets in kg.)

Yest = 1,345.09 + 1.875 (5,000)

Yest = 10,730

t-Valueα/2,df=n-2 = TINV(0.05/2,20-2)

t-Valueα/2,df=n-2 = TINV(0.975,18) = 2.3987

In Excel 2010 and beyond, TINV(α, n – 2) can also be calculated by the following Excel formula:

TINV(α, n – 2) = T.INV(1-α/2, n -2)

x_bar and SSxx are found as follows: (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)

Now we have the following:

x0 = 5,000

n = 20

Yest = 10,730

t-Valueα/2,df=n-2 = 2.3987

x_bar = 2,837.65

SSxx = 94,090,690.55

Distance value = 1/n + [(x0 – x_bar)2]/SSxx

Distance value = 1/20 + [(5,000 – 2,837)2]/94,090,690

Distance value = 0.099694

Prediction Error = Standard Error of the Regression * SQRT(1 + distance value)

Standard Error of the Regression = 1,400.463

This is found from the Excel regression output as follows: (Click On Image To See a Larger Version)

Prediction Error = 1,400.463 * SQRT(1 + 0.099694)

Prediction Error = 1,400.463 * 1.048663

Prediction Error = 1,468

Prediction Interval = Yest ± t-Valueα/2,df=n-2 * Prediction Error

Prediction Interval = 10,730 ± 2.3987 * 1,468

Prediction Interval = 10,730 ± 3,533

Prediction Interval = [ 7,197, 14,263 ]

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

#### 1 comment:

1. Click the link live cam porno free to take some rest. Billions of pretty girls are waiting for you here. Don't make them wait! Upload your photo and answer messages. You will like it! Here every of your sexual desire became real. Just try!