Normal Distribution with Excel - Applications & Examples

In my last post I discussed how to use the normal distribution function already added in MS Excel. In this post I will continue with the examples and we will how we use them. In this post i will not discuss the manual solution of the problem but only the Excel Based solution.

The following problems are taken from Introduction to Statistics By Walpole, 13th Edition Chapter 07, Page 197-200.

I wil elaborate how you can use excel formula to calculate the probabilites.

4. A soft-drink machine is regulated so that it discharges an average of 200 milliliters per cup. If the amount of drink is normally distributed with a standard deviation equal to 15 milliliters: 

(a) What fraction of the cups will contain more than 224 milliliters?
(b) What is the probability that a cup contains between 191 and 209 milliliters?
(c) How many cups will likely overflow if 230-mililiters cups are used for the next 1000 drinks.
(d) Below what value do we get the smallest 25% of the drinks? 

Solution:

Here $\mu$ = 200 mililiters and $\sigma$ = 15 mililiters

(a) =$1-Normdist(224,200,15,1) = 0.0548
(b) =Normdist(209,200,15,1)-Normdist(191,200,15,1) = 0.451
(c) =(1-Normdist(230,200,15,1))*1000 = 22.75
(d) =Norminv(0.25,200,15) = 189.89

9. The heights of 1000 students are normally distributed with a mean of 174.5 cm and a std. deviation of 6.9 cm. Assuming that he heights recorded are recorded to the nearest half of a centimeter, how many of these students would you expect to have heights,

(a) Less than 160.0 cm?
(b) Between 171.5 and 182.0 cm inclusive?
(c) Equal to 175.0 cm?
(d) Greater than or equal to 188.0 cm?

Solution:

Here $\mu$ = 174.5 cm mililiters and $\sigma$ = 6.9 cm

(a) =1000*Normdist(160,174.5,6.9,1)
(b) =(Normdist(182.5-0.5,174.5,6.9,1)-Normdist(171.5-0.5,174.5,6.9,1))*1000
(c) =(Normdist(175+0.5,174.5,6.9,1)-Normdist(175-0.5,174.5,6.9,1))*1000
(d) =(1-Normdist(188-0.5,174.5,6.9,1))*1000

Note: In (b), (c) and (d) we substracted and added 0.5 from values of $x$ to make them inclusive. We are converting point estimate into an intervel estimate through this operation.

16. The average life of a certain type of small motor in 10 years, with a stad. Deviation of 02 years. The manufacturer replaces free all motors that fail while under guarantee. If he is willing to replace only 3% of the motors that fail, how long a guarantee should he offer? Assume that the lives of the motors follow a normal distribution.

Solution:

Here $\mu$ = 10 years and $\sigma$ = 2 years and $\phi$ = 0.03 

=Norminv(0.03,10,2) = 6.238 years

The statistical concepts are self-explanatory. I hope that you are finding these posts helpful certain way.  Hope to listen your feedback soon. Take Care

Understanding Normal Distribution with Excel 2007

Normal Distribution is most commonly used distribution. In this post we try to understand how the manual calculation of Normal distribution problems can be solved using Excel's statistical functions. Excel has following four function that are related to the distribution. 

The Normal Distribution is defined by the following equation.

$$f(x)=\frac{1}{\sigma\sqrt2\pi}e^{-\frac{(x-\mu)^2}{2\sigma^2}}$$
 
Excel 2007 Provide us with following four function related to this distribution:

1. NORMSDIST()
2. NORMSINV()
3. NORMDIST()
4. NORMINV()

Firstly we will understand what each of four stand for:

1. NORMSDIST()

This function can be used to find the value of the Z-Variable as we normally see in the Table of Z-Values. For example the value of area under the Z-Cuve for Z=0.51 is 0.6950. The Same can be found using NORMSDIST(0.51). This assume mean ($\mu$) of zero and standard deviation ($\sigma$) of 1.

2. NORMSINV()

This function is used to find the Z-Value when we have area available to us. we can try with the value of area find in the above paragraph like NORMSINV(0.6950) that will be evaluated to 0.51. This assume mean of zero and standard deviation of 1.

3. NORMDIST()

This function takes the value of mean, st. deviation and an argument for cumulative or mass density function. If you put in one it will calculate cmulative probability and otherwise will give mass density function. 


4. NORMINV()

This function return the value of Z when you have area of the curve given in the data.

Example of Usage:

Following example elaborate the use of function. For $\mu$ = 50, $\sigma$ = 10 find the probability that x will lie between 45 and 62.

Using manual calculation we will do this:
$$Z_1=\frac{x-\mu}\sigma =  \frac{45-50}{10}=-0.5$$ and 

$$Z_2=\frac{x-\mu}\sigma =  \frac{62-50}{10}=1.2$$
Therefore the probability is ...

$$P(45<x<62)=P(-0.5<z<1.2)$$
$$=P(z<1.2)-P(z<-0.5)=0.8849-0.3085=0.5764$$
Now Using Excel 2007 formulas:
 
$$P(45<x<62)=Normdist(62,50,10,1)-Normdist(45,50,10,1)=0.5764$$
Example: Give a normal distribution with $\mu$ = 300 and $\sigma$ = 50 find the probability that $x$ assumes a value greater then 362.

Manual Working:$$Z=\frac{x-\mu}\sigma =  \frac{362-300}{50}=1.24$$ and 

$$P(x>362) = P(z>1.24) = 1-P(z>1.24)= 1-0.8925 = 0.1075$$
The Same result can be obtained using $=1-Normdist(362,300,50,1)$
 
Example No. 02 Given a normal distribution of $\mu$ = 40 and $\sigma$ = 6 find the value of x that has 45% of area below it.

The usual manual calculation for this will involve looking up value of $\phi =0.45$ in the Z-Table and substituting it in formula :$$-0.13=\frac{x-40}{6}=-0.13\times6+40\Rightarrow x=39.22$$ In this question we have been given area of the curve and we have been asked about the Z-Value itself. We will use the last function of the series that is Norminv() to calculate the value
$$P(x<Z)=0.45=Norminv(0.45,40,6)=39.24603$$This ends the tutorial for Normal Distribution, I hope that you will like it. Please give feedback to Improve the blog.

Thanks.