How do you perform residual analysis and check for normality and homoscedasticity in Excel?

2 Plot residuals

To plot residuals, you can use a scatter plot or a histogram in Excel. A scatter plot of residuals versus predicted values can help you visualize the relationship between the residuals and the fitted values, and detect any non-linear patterns, heteroscedasticity, or outliers. A histogram of residuals can help you assess the distribution and shape of the residuals, and check for normality, skewness, or kurtosis.

3 Test for normality

To test for normality, you can use graphical or numerical methods in Excel. Graphical methods include a normal probability plot or a Q-Q plot, which compare the observed residuals with the expected values from a normal distribution. If the points lie close to a straight line, the residuals are likely to be normally distributed. Numerical methods include statistical tests such as the Shapiro-Wilk test or the Kolmogorov-Smirnov test, which compare the observed residuals with a theoretical normal distribution and calculate a p-value. If the p-value is less than a significance level (usually 0.05), the residuals are likely to deviate from normality.

4 Test for homoscedasticity

To test for homoscedasticity, you can use graphical or numerical methods in Excel. Graphical methods include a scatter plot or a residual plot, which show the variation of the residuals across the range of predicted values. If the residuals have a constant variance, they are likely to be homoscedastic. Numerical methods include statistical tests such as the Breusch-Pagan test or the White test, which test whether the variance of the residuals depends on one or more independent variables and calculate a p-value. If the p-value is less than a significance level (usually 0.05), the residuals are likely to be heteroscedastic.

5 Identify outliers and influential points

To identify outliers and influential points, you can use graphical or numerical methods in Excel. Graphical methods include a scatter plot or a box plot, which show the range and distribution of the residuals and highlight any extreme values. Numerical methods include measures such as the standardized residuals, the leverage, the Cook's distance, or the DFFITS, which quantify the deviation, the influence, or the impact of each observation on the regression model. Generally, a standardized residual greater than 3 or less than -3, a leverage greater than 2(k+1)/n (where k is the number of independent variables and n is the sample size), a Cook's distance greater than 1, or a DFFITS greater than 2*sqrt((k+1)/n) indicate a potential outlier or influential point.

6 Improve your model

To improve your model, you can use the results of your residual analysis to diagnose and correct any problems with your regression model. For example, you can transform your variables, add or remove variables, include interaction or polynomial terms, use a different model specification, or remove or treat outliers or influential points. By doing so, you can enhance the fit, accuracy, and validity of your regression model in Excel.

7 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?