This guide will explain how to perform a Breusch-Pagan test in Excel to check for heteroscedasticity in a regression model.

##### Table of Contents

In statistics, the concept of heteroscedasticity refers to a situation where each predictor variable in a model has a different range of variability.

A classic example of heteroscedasticity can be found when modeling income and expenditure on meals. As an individual’s income increases, the variability of food consumption is likely to increase.

A person with a low income would spend a constant amount on food daily while a wealthier person may occasionally buy inexpensive meals and splurge on more expensive meals at other times.

The Breusch-Pagan test checks for heteroscedasticity by comparing the variance of the residuals of a regression model to the predicted values of the model. The test calculates a chi-square statistic based on this comparison, and the resulting p-value is used to determine whether or not there is evidence of heteroscedasticity.

We can perform the Breusch-Pagan test in Excel by using the Regression tool that comes with the Analysis Toolpak add-in. From the tool’s output, we can find the value of the squared residuals for each observation.

The squared residuals can then be used to calculate the chi-square statistic. If the p-value of the statistic is low enough, we can conclude that heteroscedasticity is present in our model.

In this guide, we will provide a step-by-step tutorial on how to perform a Breusch-Pagan test in Excel. We will cover how to find the squared residuals using the **Regression** tool and how to compute the chi-square statistic using the `CHISQ.DIST.RT`

function.

Let’s dive right in!

**A Real Example of a Breusch-Pagan Test in Excel**

Let’s explore a simple example where we may need to perform a Breusch-Pagan test in Excel.

In the table seen below, we have a dependent variable y that we want to predict using our independent variables x1, x2, and x3.

We want to know if the dataset shows signs of heteroscedasticity that may affect the accuracy of our regression model.

To conduct the Breusch-Pagan test, we need to first estimate the regression model, then calculate the squared residuals of the model.

The test then regresses the squared residuals on the independent variables of the original model and obtains an R-squared value.

We will plug this R-squared value into the `CHISQ.DIST.RT`

function to return the p-value of the given statistic. If the p-value is less than the chosen significance level, then we have sufficient evidence to conclude that heteroscedasticity is present in our model.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below.

If you’re ready to perform the Breusch-Pagan test yourself, head over to the next section to read our step-by-step breakdown on how to do it!

**How To Perform A Breusch-Pagan Test In Excel**

This section will guide you through each step you need to perform a Breusch-Pagan test in Excel.

Follow these steps to determine whether your regression model is affected by heteroscedasticity:

- First, we’ll have to activate the
**Analysis Toolpak**add-in.

In the**File**tab, click on**Options**to display the**Excel Options**dialog box.

Head over to the**Add-ins**tab and click on the button labeled ‘Go…’ to proceed. - Check the option labeled ‘Analysis ToolPak’ and click on
**OK**.

- In the
**Data**tab, click on the**Data Analysis**option. Scroll through the list of analysis tools until you find the Regression option. Click on**OK**to proceed.

- Set the Input Y Range to refer to the dependent variable of your dataset. Afterward, set the Input X Range to refer to all the independent variables in your dataset. Check the ‘Labels’ option if your selections include the column headers.

Select an output range, then click on**OK**. - The tool should now return a summary output in the indicated output range. Take note of the coefficients of each term in the equation.

- Use the coefficients to create a formula to find the predicted value of each observation.

- We can find the value of the squared residuals by squaring the difference between the observed y value and the predicted y value from the previous step.

- We’ll use the
**Regression**tool once more to find the R Square value. Instead of the observed y value, we will instead use the squared residuals as the input Y range.

- To conduct the Breusch-Pagan test, we must identify a few statistics. First, we must identify the number of observations found in our dataset. Next, we’ll need the R-square value output from the regression tool in the previous step.

We can find the Chi-Squared Test statistic by multiplying the R Square value by the number of observations. - Lastly, we’ll use the
`CHISQ.DIST.RT`

function to find the associated p-value of our test statistic.

Since our p-value is not less than 0.05, we do not have sufficient evidence to prove that heteroscedasticity is present in our model.

This tutorial should cover everything you need to know to perform a Breusch-Pagan Test in Excel.

We’ve explained how to use the Analysis Tookpak add-in to perform a multiple linear regression on a dataset. The coefficients of the regression will allow us to find the squared residual value of each observation.

We can use the squared residuals to create a new regression that we can perform a chi-squared test on to test for heteroscedasticity.

The Breusch-Pagan test is just one type of statistical test you can perform in Excel. For example, another way to analyze variance is through the Levene’s Test. You can read our step-by-step guide on how to use Levene’s Test to assess whether the variances of two or more groups are equal.

That’s all for this guide! If you’re still looking to learn more about Microsoft Excel, be sure to check out our library of Excel resources, tips, and tricks!