Understanding Negative Binomial Distribution with Excel...

in this post we are going to understand the Negative Binomial Distribution. According to Wikipedia:

"In probability theory and statistics, the negative binomial distribution is a discrete probability distribution of the number of successes in a sequence of Bernoulli trials before a specified (non-random) number of failures (denoted r) occur. For example, if we define a "1" as failure, and all non-"1"s as successes, and we throw a die repeatedly until the third time “1” appears (r = three failures), then the probability distribution of the number of non-“1”s that had appeared will be negative binomial."

The distribution can be defined by the following equation:

$$*b(x,k,p)=p^k\cdot q^{x-k}\binom{x-1}{k-1}$$ 

Here $p$ is the prbability of sucess, $k$ is the kth trial and $x$ is the number of trails.

The Excel Function that can be used for calculation of Negative Binomial Distribution is NEGBINOMDIST() that has following syntex (from the Built-in help you can find more on it!)

NEGBINOMDIST(number_f,number_s,probability_s)

..where Number_f   is the number of failures, Number_s   is the threshold number of successes, Probability_s   is the probability of a success.

Now we will take an example as-usual and will see how the manual calculation for this distribution is performed and then will move to the use of alternate excel function.

Example:

In an NBA championship series, the teams which wins four games out of seven will be the winner. Suppose that team A has probability of 0.55 of winning over the team B and both team A & B face each other in the Championship games: 

(a) What is the probability that team A will win the series in Six Games?

(b) What is the probability that team A will win the series?

(c) If both teams faces each other in a regional playoff series and the winner is decided by winning three out o f the five games, what is the probability that team A will win a Playoff?

Solution: Using the negative binomial distribution with $x=6$, $k=4$, and $p=0.55$ we get:

For (a) $*b(6,4,0.55)=\binom{5}{3}(0.55)^4(1-0.55)^2 =0.1853$

For (b) *b(4,4,0.55)+*b(5,4,0.55)+*b(6,4,0.55)+*b(7,4,0.55)

Skipping the mathematical details we can conclude it to:

$=0.0915+0.1647+0.1853+0.1668=0.6083$

For (c) *b(3,3,0.55)+*b(4,3,0.55)+*b(5,3,0.55)=0.5931

Skipping the mathematical details we can conclude it to:

$=0.1664+0.2246+0.2021=0.5931$

Alternate Solution:

Here we will see how these solutions can be obtained using NEGBINOMDIST() function:


(a) =Negbinomdist(2,4,0.55)=0.1853

(b) The probability that team A wins the series is the sum of the probabilities that matches it lost will be between zero to three (if it looses fourth match it can't win the series so the formula would be:

=Negbinomdist(3,4,0.55)+Negbinomdist(2,4,0.55)+Negbinomdist(1,4,0.55)
+Negbinomdist(0,4,0.55)

=0.1667+0.1853+0.1647+0.0915=0.6083

(c) Just like (b) the probability of a playoff for Team A is that is loosses match between 0 & 2 so the the formula will be:

=Negbinomdist(2,3,0.55)+Negbinomdist(1,3,0.55)+Negbinomdist(0,3,0.55)

=0.2021+0.2246+0.1664=0.5931 Ans.

So, that was all from me for this post, i hope you have understood the usage of Excel's built in function to facilitate the calculations. Please keep reading and give feedback to improve the block!! Take care!!