How to Construct a Prediction Interval in Excel

In statistics, simple linear regression is a technique we can use to quantify the relationship between a predictor variable, x, and a response variable, y. 

When we conduct a simple linear regression, we obtain a “line of best fit” that describes the relationship between x and y, which can be written as:

ŷ = b₀ + b₁x

where:

• ŷ is the predicted value of the response variable
• b₀ is the y-intercept
• b₁ is the regression coefficient
• x is the value of the predictor variable

Sometimes we’re interested in using this line of best fit to construct a prediction interval for a given value of x₀, which is an interval around the predicted value ŷ₀ such that there is a 95% probability that the real value of y in the population corresponding to x₀ is within this interval.

The formula to calculate the prediction interval for a given value x₀ is written as:

ŷ₀  +/-  t_α/2,df=n-2 * s.e.

where:

s.e. = S_yx√(1 + 1/n + (x₀ – x)²/SS_x)

The formula might look a bit intimidating, but it’s actually straightforward to calculate in Excel. Next, we’ll walk through an example of how to use this formula to calculate a prediction interval for a given value in Excel.

Example: How to Construct a Prediction Interval in Excel

The following dataset shows the number of hours studied along with the exam score received by 15 different students:

Suppose we would like to create a 95% prediction interval for the value x₀ = 3. That is, we want to create an interval such that there is a 95% probability that the exam score is within this interval for a student who studies for 3 hours.

The following screenshot shows how to calculate all of the necessary values to obtain this prediction interval.

Note: The formulas in column F show how the values in column E were calculated.

The 95% prediction interval for a value of x₀ = 3 is (74.64, 86.90). That is, we predict with 95% probability that a student who studies for 3 hours will earn a score between 74.64 and 86.90.

A couple notes on the calculations used:

• To calculate the t-critical value of t_α/2,df=n-2 we used α/2 = .05/2 = 0.25 since we wanted a 95% prediction interval. Note that higher prediction intervals (e.g. 99% prediction interval) will lead to wider intervals. Conversely, a lower prediction interval (e.g. 90% prediction interval) will lead to a more narrow interval.
• We used the formula =FORECAST() to obtain the predicted value for ŷ₀ but the formula =FORECAST.LINEAR() will return the exact same value.