Question

Excel functions to calculating binomial, Poisson, and normal distributions. Identify the functions and discuss how Excel can be used to calculate probabilities of X, X. Apply an example to at least one business scenario.

Answer 1

1) Binomial distribution :

We take an example of tossing a coin 6 times

here p = probability of getting head = 0.5 and q= 1-p = 0.5

Now let X be the rv : getting heads

Thus X ={0,1,2,3,4,5,6}

Thus ,

Here

Binomdist () fuction requires X value, number of trails , Probability of success, Cummulative = true or false

Here we are finding just probability of X

Thus Its =BINOMDIST(X, number of trails = 6 , probabilityof success= 1/2 , cummulative= False )

Gives the probabilities for X

If we want to find cummulative probailities,

Use BINOMDIST(X, number of trails=6, probability of success = 0.5 , Cummulative = TRUE)

We get the result as shown below,

2) Poisson distribution

let us consider we are observing the number of vehicles on road in every 1 min for 5 mins

Here x = number of vehicles,

The mean of abservation is obtained then the probabilities are obtained as below

we have X= number of vehilcles in 1 min = { 3,5,4,3,6}

Obtain mean using =average() function and varience using =var() function .

mean= 42

the function where we do not need cummulative is as,

POISSON(X, mean , cummulative= FALSE)

Now if we want the cumulative then put it as true,

Now just drag down to get all probabilities , Thus the output looks like ,

3) Normal Distribution :

Let us consider that, We have a sample of heigths in feets

X={3,3.5,4,3.2,6.2,5.2,4.2 }

Here the mean is 4.

varience is

standard deviation is

Thus the excel command to get the probabilities is

NORMDIST(x = rv , mean , standard deviation , cummulative required = true or false )

If we want to calculate the cummulative probabilities the we put Cummulative= True as shown

Thus the output looks like

Hence We get the output for cummulative probabilities according to noramal curve.