Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources
Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study
Go to the blog

Using Excel for Binomial & Hypergeometric Probabilities: STT 315 Project Instructions, Study Guides, Projects, Research of Probability and Statistics

Canterbury Christ Church University (CCCU)Probability and Statistics

Instructions for a project in stt 315 where students are required to use microsoft excel to compute binomial and hypergeometric probabilities. The project involves answering questions related to binomial and hypergeometric distributions using excel functions. The document also includes instructions on how to use excel to compute binomial probabilities and hypergeometric probabilities.

Typology: Study Guides, Projects, Research

2021/2022

Uploaded on 09/12/2022

doggy
doggy 🇬🇧

4.1

(25)

228 documents

1 / 4

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
STT 315: Project 1
Instructions and explanation
Fall 2002
1.1 General instructions
•This handout has general information about the project and has information about how to use Microsoft
Excel to compute binomial and hypergeometric probabilities.
•You will also receive a sheet with your name at the top that has questions to answer using Excel.
•Answer the questions on the sheet with your name at the top and then hand in that sheet with your answers.
You do not need to hand in this set of instructions.
•To receive full credit the answers must be handed in to your recitation instructor before the end of your
recitation on Thursday, October 31. There are 10 possible points on this project.
•If you hand in your answers to your recitation instructor between the end of recitation on Thursday,
October 31 and 5PM on Monday, November 4, you will receive 75% credit.
•If you do not hand in your answers by 5PM on Monday, November 4, you will receive no credit.
1.2 Using Excel to compute binomial probabilities
Microsoft Excel has several probability distributions available as functions, including the binomial and hypergeo-
metric distributions. We’ll investigate how to compute binomial probabilities in Excel in the context of a simple
example.
Example 1.A firm plans to contact businesses in a large city by mail as part of a survey. Surveys will be sent by
mail to a random sample of n= 200 businesses in the area. From past surveys, the firm expects that 35% of the
businesses will respond to the survey. Let Xrepresent the number of the 200 businesses that respond. We will
model Xby a binomial distribution with n= 200 and p= 0.35.
(a) What is the probability that exactly 72 businesses respond?
(b) What is the probability that the response rate is at most 0.30?
(c) What is the probability that more than 70 businesses respond?
1.2.1 Part (a)
Part (a) asks for P(X= 72). Here’s how compute this using Excel.
1. Start Microsoft Excel.
2. Click in the cell where you’d like the answer to appear.
3. Go to the Insert menu and select Function.
4. In the ensuing dialog box, choose All or Statistical as the Function Category, and the choose BI-
NOMDIST as the Function Name. Click OK.
5. A new dialog box should appear.
•Specify 200 next to Trials. This is where we specify the parameter n.
•Specify 0.35 next to Probability s. This is where we specify the parameter p.
•Specify FALSE next to Cumulative. This tells Excel that we do not want the cumulative distribution
function.
•Specify 72 next to Number s. This is what we’ve called xin the course.
•Now click OK, and you should see the answer (approximately 0.056175) in the cell you chose.
1
pf3
pf4

Partial preview of the text

Download Using Excel for Binomial & Hypergeometric Probabilities: STT 315 Project Instructions and more Study Guides, Projects, Research Probability and Statistics in PDF only on Docsity!

STT 315: Project 1 Instructions and explanation Fall 2002

1.1 General instructions

  • This handout has general information about the project and has information about how to use Microsoft Excel to compute binomial and hypergeometric probabilities.
  • You will also receive a sheet with your name at the top that has questions to answer using Excel.
  • Answer the questions on the sheet with your name at the top and then hand in that sheet with your answers. You do not need to hand in this set of instructions.
  • To receive full credit the answers must be handed in to your recitation instructor before the end of your recitation on Thursday, October 31. There are 10 possible points on this project.
  • If you hand in your answers to your recitation instructor between the end of recitation on Thursday, October 31 and 5PM on Monday, November 4, you will receive 75% credit.
  • If you do not hand in your answers by 5PM on Monday, November 4, you will receive no credit.

1.2 Using Excel to compute binomial probabilities

Microsoft Excel has several probability distributions available as functions, including the binomial and hypergeo- metric distributions. We’ll investigate how to compute binomial probabilities in Excel in the context of a simple example.

Example 1. A firm plans to contact businesses in a large city by mail as part of a survey. Surveys will be sent by mail to a random sample of n = 200 businesses in the area. From past surveys, the firm expects that 35% of the businesses will respond to the survey. Let X represent the number of the 200 businesses that respond. We will model X by a binomial distribution with n = 200 and p = 0.35.

(a) What is the probability that exactly 72 businesses respond?

(b) What is the probability that the response rate is at most 0.30?

(c) What is the probability that more than 70 businesses respond?

1.2.1 Part (a)

Part (a) asks for P (X = 72). Here’s how compute this using Excel.

  1. Start Microsoft Excel.
  2. Click in the cell where you’d like the answer to appear.
  3. Go to the Insert menu and select Function.
  4. In the ensuing dialog box, choose All or Statistical as the Function Category, and the choose BI- NOMDIST as the Function Name. Click OK.
  5. A new dialog box should appear.
    • Specify 200 next to Trials. This is where we specify the parameter n.
    • Specify 0.35 next to Probability s. This is where we specify the parameter p.
    • Specify FALSE next to Cumulative. This tells Excel that we do not want the cumulative distribution function.
    • Specify 72 next to Number s. This is what we’ve called x in the course.
    • Now click OK, and you should see the answer (approximately 0.056175) in the cell you chose.

1.2.2 Part (b)

For Part (b) we need to do a bit of work setting up the problem before Excel can help. Since the response rate is X/200, we want P (X/ 200 ≤ 0 .30).

Multiply both sides by 200 to rewrite this in terms of X:

P (X ≤ 60).

So we want the value of the cumulative distribution function at 60. To obtain this from Excel, repeat the steps above, with the following changes:

  • Specify TRUE next to Cumulative.
  • Specify 60 next to Number s.

Your answer should be 0.0783.

1.2.3 Part (c)

For Part (c) we want P (X > 70). This is equal to 1−P (X ≤ 70) by the complement rule. So compute P (X ≤ 70) in Excel, and then subtract the answer from one, either by hand or using Excel. Your answer should be 0.46753.

1.3 Using Excel to compute hypergeometric probabilities

Example 2. A shipment of 350 parts contains 22 defective parts. An inspector chooses 40 parts at random to be inspected.

(a) What is the probability that exactly 2 of the inspected parts are defective?

(b) What is the probability that more than 5 of the inspected parts are defective?

1.3.1 Part (a)

To answer Part (a), we need to compute P (X = 2), where X represents the number of defective parts in the 40 parts inspected. We know that X has a hypergeometric distribution with N = 350, S = 22, n = 40. Here’s how to compute the probability using Excel.

  1. Start Microsoft Excel.
  2. Click in the cell where you’d like the answer to appear.
  3. Go to the Insert menu and select Function.
  4. In the ensuing dialog box, choose All or Statistical as the Function Category, and the choose HYPGE- OMDIST as the Function Name. Click OK.
  5. A new dialog box should appear.
    • Specify 2 next to Sample s. This represents the number of “successes” we want in our sample.
    • Specify 40 next to Number sample. This is where we specify the sample size n
    • Specify 22 next to Population s. This is where we specify the number of “successes” S in the population.
    • Specify 350 next to Number pop. This is where we specify the population size N.
    • Now click OK, and you should see the answer (approximately 0.27408) in the cell you chose.

1.4 Some cautions

For some values of n and p, when n is rather large (a bit over 1000), Excel has trouble computing the appropriate binomial probabilities. Similarly, when N is large (again, a bit over 1000), Excel has trouble computing the appropriate hypergeometric probabilities. Unfortunately,

  • Excel does not give a meaningful error message to inform the user that something has gone wrong; and
  • the behavior is erratic, as the following example shows.

Example 3. Let X have a binomial distribution with parameters n = 1030 and p = 0.5. We will try to compute P (X = 498), P (X = 499), P (X = 500), and P (X = 501) using Excel.

  1. Put the numbers 498, 499 , 500 , 501 in Cells D1 through D4.
  2. Click on Cell E1.
  3. Compute P (X = 498) in Cell E1 using the BINOMDIST function in Excel. You should get an answer of 0 .014187225.
  4. Using copy and paste as above, copy Cell E1 to Cells E2 through E4. In theory, this should put P (X = 499), P (X = 500), and P (X = 501) in these cells.
  5. What happens???

The example above, and other examples where Excel does not do probability and statistical calculations properly, can be found in a paper by Leo Kn¨usel of the University of Munich. The paper and related papers are available on the web at http://www.stat.uni-muenchen.de/~knusel/elv/accuracy.html.