XLSTAT: Simple Linear Regression in Excel

 

Background

Simple linear regression enables you to predict a variable depending on another variable, on the basis of a linear relationship inferred by a supervised learning algorithm.
If you are trying to predict a variable depending on several others, do not hesitate to read our tutorial on Multiple Linear Regression.

 

Dataset for Example

The data from Lewis and Taylor (1967) include 237 children’s gender, age, height, and weight. Using simple linear regression with weight as the dependent variable and height as the explanatory variable, we assess whether a linear model is appropriate.

 

Setting Up

• Open XLSTAT.

• Click on the Modeling data menu and select the Linear Regression feature.

In the General tab:

• Select Weight as the Dependent variable.

• Select Height as the Quantitative explanatory variable.

• Since the column title for the variables is already selected, leave the Variable labels option activated.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first table shows goodness-of-fit coefficients. The R² indicates the percentage of variability in the dependent variable explained by the model; values closer to 1 signify a better fit.

In this case, 60% of weight variability is explained by height. The remaining variability is due to other factors not included in the model.

The ANOVA table tests whether the explanatory variable provides significant information, helping to decide if using the mean alone is sufficient or if the variables add value.

Since the p-value is less than 0.0001, we can confidently conclude that the three variables provide significant information. The model details show a narrow 95% confidence interval for height, indicating precise estimation; the intercept has a wider interval. The model equation indicates that, within the studied height range, each additional inch increases weight by approximately 3.8 pounds.

The residuals table shows that 9 out of 237 standardized residuals fall outside the [-1.96, 1.96] interval, indicating potential outliers but not rejecting normality. The first chart visualizes the data, regression line, and two confidence intervals: one on the mean prediction and one on individual predictions, highlighting a linear trend with high variability. The outliers also fall outside the individual prediction interval.

The third chart plots standardized residuals against height. If a trend appears, it suggests model misspecification or residual autocorrelation, which violates linear regression assumptions.

The next chart allows to compare the predictions to the observed values. The confidence limits allow, as with the regression plot displayed above, to identify outliers.

The histogram of the residuals enables us to quickly visualize the residuals that are out of the range [-2, 2].

 

Conclusion

Height explains 60% of weight variability, but much remains unexplained. To improve the model, the Age variable could be added in a multiple linear regression example.