Real Statistics Data Analysis Tool: The Real Statistics Resource Pack supplies a Poisson Regression data analysis tool that automates the process of creating a Poisson regression model in Excel. In particular, it uses Newton’s method to estimate the regression coefficients.
Input
For example, to perform the analysis for Example 1 of Poisson Regression using Solver, press Ctrl-m and double-click on the Regression option in the dialog box that appears (or click on the Reg tab if using the Multipage user interface). Next, select the Poisson Regression option in the dialog box that appears and click on the OK button. Finally, fill in the dialog box that appears as shown in Figure 1, and press the OK button.
Figure 1 – Poisson Regression dialog box
Output
The output is shown in Figures 2 and 3.
Figure 2 – Poisson Regression analysis (part 1)
Figure 3 – Poisson Regression analysis (part 2)
Formulas
Note that LLmin (cell V13) is calculated by the formula
=SUMPRODUCT(AE4:AE18,LN(K4:K18))+AF19*LN(AE19/ SUM(K4:K18))-AE19-SUMPRODUCT(LN(FACT(AE4:AE18)))
LLfit (cell V14) is calculated by the formula
=SUMPRODUCT(MMULT(DESIGN(G4:I18),N4:N7)+LN(K4:K18), AE4:AE18)-SUMPRODUCT(EXP(MMULT(DESIGN(G4:I18),N4:N7)), K4:K18)-SUMPRODUCT(LN(FACT(AE4:AE18)))
LLmax (cell V15) is calculated by the formula
=SUMPRODUCT(AE4:AE18,LN(AE4:AE18))-AE19-SUMPRODUCT(LN(FACT(AE4:AE18)))
Note too that the diagonal of the hat matrix (range AL4:AL18) is calculated by the array formula
=DIAG(MMULT(MMULT(AF4:AF18*DESIGN(G4:I18), MINVERSE(MMULT(TRANSPOSE(DESIGN(G4:I18)), AF4:AF18*DESIGN(G4:I18)))),TRANSPOSE(DESIGN(G4:I18))))
The covariance matrix (range V4:AB7) is calculated by the array formula
=MINVERSE(MMULT(TRANSPOSE(DESIGN(G4:I18)), AF4:AF18*DESIGN(G4:I18)))
Finally, the convergence vector (range AC4:AC7) is calculated by the array formula
=MMULT(Y4:AB7,MMULT(TRANSPOSE(DESIGN(G4:I18)),AG4:AG18))
The values in this array should be close to zero, demonstrating that the model converged to a solution within the specified number of iterations.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
References
Hintze, J. L. (2007) Poisson regression. NCSS
https://www.ncss.com/wp-content/themes/ncss/pdf/Procedures/NCSS/Poisson_Regression.pdf
Nussbaum, E. M., Elsadat, S., Khago, A. H. (2007) Best practices in evaluating count data, Chapter 21: Poisson regression.
http://www.academia.edu/4387460/BEST_PRACTICES_IN_ANALYZING_COUNT_DATA_Poisson_Regression
Penn State (2017) Poisson regression. STAT 504: Analysis of discrete data.
https://online.stat.psu.edu/stat504/lesson/9
I think the phi correction is incorrectly applied to adjust the se values in the tool. It should be adjusted by the sqrt(phi), not phi itself. Multiplying the variance matrix by the given phi value does not result in the same adjusted se value given in the results table.
James,
Yes, you are correct. The standard error is adjusted by the square root of phi.
I thought that I had done that. Can you point me to a place where I adjusted the se by phi and not by the sqrt(phi)?
Charles
James,
If I multiply the variance matrix by the constant phi then the variances will be multiplied by phi, doesn’t that mean that the standard errors will be multiplied by sqrt(phi)?
Charles