Introduction
In Negative Binomial Regression Tool (Solver) we introduced the Negative Binomial Regression data analysis tool and showed how to use it to build a regression model for count data. In addition to the Solver-based version of the tool, this tool also provides a version that uses Newton’s method to estimate the coefficients and their standard errors. We describe this version here.
Data Analysis Tool
To use the Newton method’s option, you press the key sequence Ctrl-m and select the Negative Binomial Regression option from the Reg tab as before. This brings up the dialog box shown in Figure 1 of Negative Binomial Regression Tool (Solver).
This time you don’t check the Use Solver option on the dialog box. The output is similar to that shown in Figures 2-5 of Negative Binomial Regression Tool (Solver).
There are two main differences:
(1) The coefficient/parameter table in range I3:O9 of Figure 2 from that webpage is filled by the array formula
=NegBinomCoeff(D1:G13,B1:B13,TRUE,C1:C13,TRUE,R20,V11)
instead of by Solver, and
(2) Cell R13 in Figure 2 is now filled in by the formula
=NegBinomLL0(AC4:AC15,C2:C13)
instead of by Solver.
Worksheet Functions
Real Statistics Functions: The Real Statistics Resource Pack provides the following array functions pertaining to a Negative Binomial regression model based on the data in X range Rx (with k columns), Y range Ry and frequency range Rt.
NegBinomCoeff(Rx, Ry, lab, Rt, head, alpha, iter, guess): returns a k+2 × 6 array containing the estimated regression coefficients, standard errors, Wald statistic, p-value and the lower and upper ends of the 1 – alpha confidence interval; if head = TRUE (default FALSE) then the first row of Rx, Ry and Rt are column headers.
If lab = TRUE, then an extra column is appended to the output containing labels. alpha is the significance level (default .05). iter (default 800) is the number of iterations used in calculating the coefficients using Newton’s method. guess is a column array containing the initial guess of these coefficients (if missing then all the coefficients are initially set to zero and dispersion alpha is set to .5).
NegBinomLL0(Ry, Rt, iter, guess): returns LLmin
iter (default 800) is the number of iterations used in calculating the intercept coefficient and dispersion alpha using Newton’s method. guess is a 2 × 1 array containing the initial guess of the intercept coefficient and dispersion alpha (if missing then the intercept coefficients is initially set to zero and the dispersion alpha is set to .5).
Fixed Alpha Value
Finally, the Negative Binomial Regression data analysis tool provides one more option. In this case, the alpha dispersion value is preassigned and the regression coefficients are calculated using Newton’s method.
E.g. for Example 1 of Negative Binomial Regression Tool (Solver) if you want to preassign the alpha value to .104035, you fill in the dialog box as shown in Figure 1 on that webpage except that you leave the Use Solver option unchecked and fill in the Dispersion Param field with this value. Note too that if you don’t change the default value for the Iter field from 800 then it will automatically be reset to 25.
The output is the same as that described previously, except that the output in Figure 2 of Negative Binomial Regression Tool (Solver) is replaced by that shown in Figure 1.
Figure 1 – Negative Binomial Regression with fixed Alpha
Range I3:O8 contains the array formula
=NegBinomCoeff0(D1:G13,B1:B13,TRUE,C1:C13,TRUE,R20,V11,J9)
Cell R13 contains the formula =NegBinomLL0(AC4:AC15,C2:C13), which means that this cell maximizes the value of LL based on the intercept coefficient and the alpha value. Cell S13 contains the formula =NegBinomLL0X(AC4:AC15,J9,C2:C13), which means that this cell maximizes the value of LL based on the intercept coefficient only with the alpha value fixed.
Finally, the covariance matrix that is output is only for the regression coefficients (and not alpha).
Worksheet Function
Real Statistics Function: The Real Statistics Resource Pack provides the following array functions pertaining to a Negative Binomial regression model based on the data in X range Rx (with k columns), Y range Ry and frequency range Rt.
NegBinomCoeff0(Rx, Ry, lab, Rt, head, alpha, iter, guess): returns a k+1 × 6 array containing the estimated regression coefficients, standard errors, z-statistic, p-value and the lower and upper ends of the 1 – alpha confidence interval; if head = TRUE (default FALSE) then the first row of Rx, Ry and Rt are column headers.
If lab = TRUE, then an extra column is appended to the output containing labels. alpha is the significance level (default .05). iter (default 20) is the number of iterations used in calculating the coefficients using Newton’s method. guess is a column array containing the initial guess of these coefficients (if missing then all the coefficients are initially set to those for Poisson regression and dispersion alpha is set to .5).
NegBinomLL0X(Ry, alpha, Rt): returns LLmin where only the intercept coefficient can vary
Here alpha is the dispersion parameter. LLmin is calculated in a similar manner as for Poisson regression.
Examples Workbook
Click here to download the Excel workbook with the examples described on this webpage.
References
Hilbe, J. M. (2007) Negative binomial regression. Cambridge University Press
https://nzdr.ru/data/media/biblio/kolxoz/M/MV/MVsa/Hilbe%20J.M.%20Negative%20Binomial%20Regression%20(CUP,%202007)(ISBN%209780521857727)(O)(263s)_MVsa_.pdf?ysclid=lkq9gjqlwg287891004
Hilbe, J. M. (2014) Modeling count data. Cambridge University Press
https://www.cambridge.org/core/books/modeling-count-data/BFEB3985905CA70523D9F98DA8E64D08
Hintze, J. L. (2007) Negative binomial regression. NCSS
https://www.ncss.com/wp-content/themes/ncss/pdf/Procedures/NCSS/Negative_Binomial_Regression.pdf
Zwilling, M. (2013) Negative binomial regression
https://www.researchgate.net/publication/270040724_Negative_Binomial_Regression