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!

14 comments:

  1. I am amazed at the advanced functionalities provided by MS Excel. While Office setup is simple task, mastering them could take weeks, months or even years. Thank you for sharing.

    ReplyDelete
  2. X Required. The value for which you want the distribution.

    On what basis do I enter x? How do I choose this required value?

    ReplyDelete
  3. Each year, the people of France consume 0.4 litre of alcohol per capita, and this volume consists of 18.8% beer, 56.4% wine, 23.1% distilled spirits, 1.7% other alcohol. http://www.confiduss.com/en/jurisdictions/france/culture/

    ReplyDelete
  4. economics assignment writing service offers you the most affordable but excellent custom writing services & assistance by professional qualified writers of USA.

    ReplyDelete
  5. Crime in London asset protection in London
    is rising five times faster than the rest of England, so it’s more important than ever to ensure your safety through hiring personal protection with us.

    ReplyDelete
  6. Hi, I think your website might be having browser compatibility issues.When I look at your blog in Opera, it looks fine but when opening in Internet Explorer, it has some overlapping.I just wanted to give you a quick heads up! Otherthen that, amazing blog!

    ReplyDelete
  7. Users may face problems and troubles while interacting with these tools and functions. These problems and difficulties impede QuickBooks’ processing. This is where the QuickBooks file doctor can help sp quickbook file doctor download download is the outstanding tool which can resolve all your problem.

    ReplyDelete
  8. We all know and adore QuickBooks, which is one of the most popular accounting software programs. The Quickbooks update error 404 is a warning message that appears on the screen. A QuickBooks service notice indicating Quickbooks error 404 cannot perform this update is usually displayed when an update error occurs. The main source of this issue is that QuickBooks occasionally fails to sync data with Intuit Servers, resulting in an error 404 for the user.

    ReplyDelete
  9. Accounting is usually challenging, particularly when starting a company from scratch, which is why accounting software packages are in high demand. quickbooks database server manager and Quicken are the two main brands in this region. Yes, it can be difficult to make up your mind on the best accounting software kit with high accounting requirements. This is precisely why we have produced this fantastic contrast between the two famous apps. We’ll see a more in-depth analysis of Quicken vs Quickbooks and we’ll go over the fine point of each program. This means you won’t have to spend time looking for answers on various websites; you’ll find them all right here.

    ReplyDelete
  10. Great article I should say and appreciation to the data.The best sensitivity for  warzone While most sensitivity settings vary from 0 to 20, the best controller players in the game set theirs to around 7.2 on average. Your sensitivities should ideally be slightly higher than the default of 5, but not so high that you lose control.

    ReplyDelete
  11. I think your blog will easily get the correct market because it's having the piece of content. so it can easily attract visitors.
    If you are looking to buy customised eco friendly plantable seed pencil. Get connected with us for more details.

    ReplyDelete
  12. Such an awesome and cool post. Thanks for sharing this one! fence contractors Metairie, LA

    ReplyDelete