Neg Binomial Reg Predictions | Real Statistics Using Excel

Introduction

Once you calculate the regression coefficients and covariance matrix, the prediction of the count for any set of regressors can be calculated using the Poisson functions PoissonPredC and PoissonPredCC described in Poisson Regression Predictions.

Example

Example 1: Use the negative binomial regression results for Example 1 of Negative Binomial Regression Tool (Solver), as shown in Figure 1, to count how many passengers from the Titanic would survive with characteristics shown in range K2:O6 of Figure 2.

Figure 1 – Negative Binomial regression for Titanic data

Figure 2 – Predictions

Here, the predicted survival counts in range P3:P6 are calculated by the array formula =PoissonPredC(L3:O6,B3:B7,K3:K6). The expanded results, as shown in range P9:S13, are calculated by the array formula

=PoissonPredCC(L10:O13,B3:B7,D3:H7,TRUE,K10:K13)

The first two data rows in Figure 2 correspond to entries 1 and 12 from Figure 1 of Negative Binomial Regression Tool (Solver). We see the predictions are the same as those shown in rows 4 and 14 of Figure 5 from that webpage.

As usual, these functions can also be used with data that was not in the original sample. For this example, all possible combinations of the four regressors are already included in the data, and so we can only vary the # of cases as in done in the last two data rows in Figure 2.

Probabilities

Since we are assuming that the data fits a negative binomial distribution, we can use the model to determine the probability that a specific number of counts occur.

As described above, the specific negative binomial distribution is based on the parameters

ν = 1/α p = ν/( ν+μ)

Thus, for a titanic passenger with Age = 0, Sex = 0, Class = 3, we see from Figure 2 that μ = 23.11074. Since α = .104034 and ν = 9.61215, it follows that the probability that such a person survives can be calculated by the formula

=NEGBINOM.DIST(x, ν, p, FALSE)

Caution

When Excel calculates the pdf or cdf of the negative binomial distribution it rounds x and ν down to the nearest integer. This is particularly a problem when ν < 0. To avoid this problem, instead of using the =NEGBINOM.DIST(x, ν, p, FALSE) formula, you can use one of the following alternatives:

=EXP(GAMMALN(x+ν)-GAMMALN(x+1)-GAMMALN(ν)+ν*LN(p)+x*LN(1-p))

=EXP(GAMMALN(x+ν)-GAMMALN(x+1)-GAMMALN(ν))*p^ν*(1-p)^x

Note that the formula NEGBINOM.DIST(x, ν, p, TRUE) can be replaced by

=BETA.DIST(p, ν, x+1, TRUE)

Where x = 0, you can replace =NEGBINOM.DIST(x, ν, p, FALSE) by

=BETA.DIST(p, ν, 1, TRUE)

For count values of x > 0, you can use

=BETA.DIST(p, ν, x+2, TRUE)-BETA.DIST(p, ν, x+1, TRUE)

See Negative Binomial Distribution for details.

Worksheet Functions

Real Statistics Functions: The Real Statistics Resource Pack provides the following worksheet functions. These pertain to a negative binomial regression model based on the coefficients in Rc, and the X data in Rx (with k columns), Y count data in Ry, and frequency data in Rt. If Rt is omitted it defaults to a column of ones. Rc is a k+2 × 1 array, whose last entry is the alpha dispersion parameter.

NegBinomPredC(Rx, Rc, Rt): returns a column array with the forecasts for the profiles in Rx/Rt

NegBinomPredCC(Rx, Rc, lab, Rt, alpha): returns an array with 6 columns where each row contains the forecast values for the corresponding profile from Rx/Rt along with the prediction, s.e. and the lower and upper ends of the 1-alpha confidence interval for the forecast. If lab = TRUE (default FALSE) then an extra row of headings is appended to the output. alpha defaults to .05. If Rt is omitted it defaults to a column of ones.

NegBinomProb(Rx, Rc, Ry, Rt): returns a column array with the probabilities for the profiles in Rx/Rt and counts in Ry. If Ry is omitted then it defaults to a column of zeros. You can also set Ry to a single value, in which case all the counts are set to this value (default 0).

Note that the NegBinomProb function uses the standard Excel NEGBINOM.DIST function and so the value of ν = 1/α is truncated to the next lower integer (see Caution above). This will be addressed in a future release of the Real Statistics Resource Pack.