Tuesday, June 3, 2014

Solving Normal Distribution Problems in Excel 2010 and Excel 2013

This is one of the following eight articles on the normal distribution in Excel

Overview of the Normal Distribution

Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013

Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013

Solving Normal Distribution Problems in Excel 2010 and Excel 2013

Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013

An Important Difference Between the t and Normal Distribution Graphs

The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean

Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An Easy-To-Understand Way

Solving Normal

Distribution Problems in

Excel

 

Problems Using NORM.DIST() To

Calculate F(X,µ,σ)

F(X,µ,σ) = NORM.DIST(X,µ,σ,TRUE)

 

Problem 1

A certain brand of automobiles has normally-distributed fuel consumption. The brand’s mean fuel consumption is 27 miles-per-gallon and standard deviation is 5 miles-per-gallon. What percentage of automobiles of this brand can be expected to have fuel consumption between 25 and 30 miles-per-gallon?

0.3812 = NORM.DIST(30,27,5,TRUE) - NORM.DIST(25,27,5,TRUE)

0.3812 = 0.7257 – 0.3446

38.12 percent of autos of the brand have fuel consumption between 25 and 30 miles-per-gallon as shown in the following Excel-generated graph:

normal distribution, statistics, excel, excel 20101, excel 2013
(Click On Image To See a Larger Version)

 

Problem 2

A company packages potatoes into knitted net bags for shipment to supermarkets. The weight of the filled bags of potatoes is normal-distributed with a mean of 20 pounds and a standard deviation of 1 pound. What is the probability that a randomly selected bag of potatoes will either weigh more than 22 pounds or less than 18.5 pounds?

Probability that potatoes weigh less than 18.5 pounds =

NORM.DIST(18.5,20,1,TRUE)

Probability that potatoes weigh less than 18.5 pounds = 0.0668 = 6.68 %

Probability that potatoes weigh less than 22 pounds = NORM.DIST(22,20,1,TRUE)

Probability that potatoes weigh more than 22 pounds = 1 - NORM.DIST(22,20,1,TRUE)

Probability that potatoes weigh more than 22 pounds = 1 - 0.9773 = 0.0227 = 2.27 %

Probability that the potatoes weigh less than 18.5 pounds OR more than 22 pounds equals

Probability that the potatoes weigh less than 18.5 pounds = 0.0668

PLUS

Probability that the potatoes weigh more than 22 pounds = 0.0227

Which equals

NORM.DIST(18.5,20,1,TRUE) + [1 - NORM.DIST(22,20,1,TRUE) ]

Which equals

0.0668 + 0.0227 = 0.0896 = 8.96 %

8.96 percent of bags of potatoes have weights outside of 18.5 pounds and 22 pounds as shown in the following Excel-generated graph:

normal distribution, statistics, excel, excel 20101, excel 2013
(Click On Image To See a Larger Version)

 

Problems Using NORM.INV() To

Calculate X Given F(X,µ,σ)

NORM.INV[ F(X,µ,σ), µ, σ ] = X

 

Problem 1

A tire company makes a tire with a normally-distributed tread life. The mean tread life is 39,000 miles and the standard deviation of tread life is 5,300 miles. What tread life would be exceeded by only 3 percent of all tires?

48968 = NORM.INV(0.97,39000,5300)

97 percent of all tires will wear out before they are driven 48,968 miles hours as shown in the following Excel-generated graph:

normal distribution, statistics, excel, excel 20101, excel 2013
(Click On Image To See a Larger Version)

 

Problem 2

A company’s package delivery time is normal-distributed and has a mean of 10 hours and a standard deviation of 3 hours. What delivery time will be beaten by only 2.5 percent of all deliveries?

4.120 = NORM.INV(0.025,10,3)

Only 2.5 percent of all deliveries are made quicker than 4.120 hours as shown in the following Excel-generated graph:

normal distribution, statistics, excel, excel 20101, excel 2013
(Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

 

Click Here To See a List Of All

Statistical Topics And Articles In

This Blog

 

You Will Become an Excel Statistical Master!

1 comment: