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:

regression,prediction interval,statistics excel,excel 2010,excel 2013
(Click On Image To See a Larger Version)

regression,prediction interval,statistics excel,excel 2010,excel 2013
(Click On Image To See a Larger Version)

regression,prediction interval,statistics excel,excel 2010,excel 2013
(Click On Image To See a Larger Version)

regression,prediction interval,statistics excel,excel 2010,excel 2013 (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:

regression,prediction interval,statistics excel,excel 2010,excel 2013
(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

 

Posted by at

1 comment:

  1. AnonymousDecember 19, 2016 at 2:53 AM

    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!

    ReplyDelete

Subscribe to: Post Comments (Atom)