XLSTAT: Log-linear Regression (Poisson) in Excel

 

Dataset for Example

The attached data correspond to the number of awards earned by students at one high school. To explain the number of awards earned, there are two predictors: the type of program in which the student was enrolled (e.g., vocational, general or academic) and the score on their final exam in math.

For count data, a log-linear regression using a Poisson distribution is appropriate to model and predict the number of awards earned by a student.

 

Setting Up

• Open XLSTAT.

• Click on the Modeling data menu and select the Log-linear regression feature.

• Once you've clicked on the button, the dialog box appears.

In the General tab:

• The data are presented in 200 rows and 3 columns table. The first column is the response variable, and the two others are the explanatory variables.

In the Options tab:

• Several criteria about the algorithm can be modified. It is also possible to add interactions between the variables.

• Here we choose to leave all the options to their default values.

• 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 statistics for each variable, with the dependent variable in blue.

The next table assesses model quality. The key value is the Chi-square test probability on the log ratio—analogous to the F-test in linear regression. Since this value is below 0.0001, the variables provide significant information beyond a constant-only model.

The following table gives the estimated value of the coefficients for the fitted model. To evaluate if a variable bring significant information, a statistical test is displayed. In our case, we note that all parameters differs significantly from 0 (at a 5% level).

The last step involves applying the fitted model to the entire population dataset. This means using the estimated coefficients from the regression to predict or explain the dependent variable for all individuals or observations in the population.

This application allows us to assess the overall model performance, make predictions, and draw conclusions based on the full dataset rather than just the sample used for model estimation. It also enables the identification of patterns or trends across the entire population and helps in validating the model’s generalizability.