CODEWITHSUNDEEP
rexcel
rasty5
rasty5

Reputation: 33

Translate Negative Binomial parameters from R to Excel

I applied the fitdistrplus package in order to fit an empirical distribution. It turned out the best fit was the negative binomial distribution with parameters:

size=0.6900788
mu=2.6522087

dnbinom(0:10, mu = 2.6522087, size =0.6900788)
[1] 0.33666338 0.18435650 0.12362301 0.08796440 0.06439416 0.04793144 0.03607044 0.02735574 0.02086667 0.01598815 0.01229390

I am now trying to generate the same numbers on EXCEL where the parameters are required in different format:

NEGBINOMDIST(number_f,number_s,probability_s)

How am I meant to do this? Any ideas? Many thanks..

Upvotes: 2

Views: 1450

Answers (1)

Ben Bolker
Ben Bolker

Reputation: 226771

According to Microsoft's documentation, Excel uses the standard "number of draws before n failures" definition; the parameterization used by fitdistrplus is the alternative referred to in ?dnbinom as:

An alternative parametrization (often used in ecology) is by the mean ‘mu’, and ‘size’, the dispersion parameter, where ‘prob’ = ‘size/(size+mu)’. The variance is ‘mu + mu^2/size’ in this parametrization.

So if you want to get back from mu and size to prob and size (Excel's probability_s and number_s respectively) you need

number_s=size
probability_s=size/(size+mu)

muval <- 2.6522087
sizeval <- 0.6900788
(probval <- sizeval/(sizeval+muval))
## [1] 0.206469
all.equal(dnbinom(0:10,mu=muval,size=sizeval),
          dnbinom(0:10,prob=probval,size=sizeval))
## TRUE

However, you're not done yet, because (as commented above by @James) Excel only allows positive integers for number_s, and the estimated value above is 0.69. You may need to search/ask on an Excel-related forum about how to overcome this limitation ... at worst, since Excel does have an implementation of the gamma function, you can use the formula given in ?dnbinom

Gamma(x+n)/(Gamma(n) x!) p^n (1-p)^x

to implement your own calculation of the NB (this formulation allows non-integer values of n). It would be best to use the GAMMLN function in Excel to calculate the numerator and denominator of the normalization constant on the log scale ... if you're lucky, someone out there will have saved you some trouble and implemented this already ...

Upvotes: 3

Related Questions