Column Width Needs to Be Formatted So That All Labels Can Be Read Clearly

Lab #6

STAT105

·  Column width needs to be formatted so that all labels can be read clearly.

·  All answers must be rounded to 8 decimal places.

1.  Open up a new worksheet in Excel, type: Your name in cell A1

(0.5 point) Stat105 Lab 6 in cell A2

2.  (1 point) Powerball is a multi state lottery. The following probability distribution represents the cash prizes of Powerball with their corresponding probabilities, where the grand prize for this drawing was 15 million dollars. If you were to play the game over and over again, your expected net revenue would be the mean of the distribution. Use Excel to find the mean of the following discrete probability distribution.

Copy the table over to your Excel spreadsheet below your name. In the column next to the probabilities you want to multiply x by the corresponding probability and then add them up. There is a quick function called the SUMPRODUCT that will do this in one step. See below. Be careful w/ cell references if you start your table in a different spot.

Turn to page 129 of your text. To use Excel to find the probability under a standard normal distribution, use the function =Normsdist(z). For example to find percentage (probability) of less than z = -1.43 or the P( z < -1.43) in Excel type in =Normsdist(-1.43) and the computer will return 0.0764. This is illustrated in Figure 7.2. To find the percentage (probability) of less than 9 years for a normal distribution with a mean of 11.1 years and a standard deviation of 1.4 years (example on page 128) use the function =Normdist(x,mean,stand_dev,1) and you would avoid the typo that the book made in the example problem. When they calculated the z-score, it should have been z = (9-11.1)/1.4 = -1.5. When you drop the “s” and just use Normdist than you do not have to find the z-score. We could have found the correct answer as =Normdist(9,11.1,1.4,1) and you get 0.0668.

If you need to find a value greater than, or greater than or equal to use the complement rule and take =1-Normsdist(z) or =1-Normdist(x,mean,stand_dev,1).

To find the area between two values, find =Normsdist(z) for both numbers and then in a separate cell subtract the smaller area from the larger area. (Same for the =Normdist).

3.  (0.5 point each) Use Excel to find probabilities for the Normal Distribution. Make sure to round your answers to 8 decimal places

a)  P( z < 2.5)

b)  P( z < -1.06)

c)  P( z > 2.41)

d)  P( z > -0.58)

e)  P( -2.63 < z < -1.59)

f)  P( -1.57 < z < 2.67)

4.  (1 point each) Use Excel to find probabilities the following example. Make sure to round your answers to 8 decimal places The average daily jail population in the United States is 618,319. If the distribution is normal and the standard deviation is 50,200, find the probability that on a randomly selected day the jail population is:

a)  Greater than 700,000.

b)  Between 500,000 and 600,000.

Excel will go backwards and find the x-value for a certain percent or probability. Example: To qualify for security officers’ training, recruits are tested for stress tolerance. The scores are normally distributed with a mean of 62 and a standard deviation of 8. If only the top 15% of recruits are selected, find the cutoff score. In excel use the function =NORMINV(1-0.15,62,8) and you get the cutoff score of 70.29. The first value in the function is the area to the left of your x value; since we want the top 15% then we use the complement rule 1-.15 = .85 or the bottom 85%. The next two values are the mean and standard deviation of the distribution.

5.  (1 point each) A mandatory competency test for high school sophomores has a normal distribution with a mean of 400 and a standard deviation of 100. Make sure to round your answers to 8 decimal places

a)  The top 3% of students receive a scholarship. What is the minimum score you would need to receive the scholarship?

b)  The bottom 1.5% (.015) of students must go to summer school. What is the minimum score that you would need to stay out of this group?

To find the z-scores used in a confidence interval you need the lower and upper values that would contain the middle 95% of the normal distribution. First we need to know how much area would be in the lower tail. The entire distribution has an area of 1, so take 1-0.95 = 0.05 and we get the area in both the lower and upper tails. The lower tail is half of this so 0.05/2 = 0.025. Now use the function =NORMSINV(0.025) to get the z = -1.95996399 or approximately z = -1.96 as the text uses in their examples, to get the other z-score use =NORMSINV(1-0.025).

6.  (1.5 points) Use Excel to find the 2 z-scores used in a 90% confidence interval. Make sure to round your answers to 8 decimal places.