Data analytics

3/18/2020 Solved: It is widely known that many drivers on interstate high... | Chegg.com

https://www.chegg.com/homework-help/data-analysis-and-decision-making-with-microsoft-excel-revised-3rd-edition-chapter-6-problem-13p-solution-9… 1/7

home / study / math / statistics and probability / statistics and probability solutions manuals / data analysis and decision making with microsoft excel, revised / 3rd edition / chapter 6 / problem 13p

Data Analysis and Decision Making with Microsoft Excel, Revised (3rd Edition)

See this solution in the app

Problem

It is widely known that many drivers on interstate highways in the United States do not observe the posted speed limit. Assume that the actual rates of speed driven by U.S. motorists are normally distributed with mean mph and standard deviation 5 mph. Given this information,

answer each of the following independent questions. (Hint: Use Goal Seek in parts a and b, and use the Solver add-in with no objective in part c. Solver is usually used to optimize, but it can also be used to solve equations with multiple unknowns.)

a. If 40% of all U.S. drivers are observed traveling at 65 mph or more, what is the mean

b. If 25% of all U.S. drivers are observed traveling at 50 mph or less, what is the mean

c. Suppose now that the mean and standard deviation of this distribution are both unknown. Furthermore, it is observed that 40% of all U.S. drivers travel at less than 55 mph and 10% of all U.S. drivers travel at more than 70 mph. What must be?

Step-by-step solution

Many drivers on Interstate highways in the United States do not observe the posted speed limit, therefore it is assumed that the actual rates of speed driven by U.S. motorists are normally distributed with mean mph and standard deviation mph.

Comment

Let be the random variable that denotes the actual rates of speed driven by U.S. motorists. So follows a normal distribution with mean mph and standard deviation mph.

Symbolically,

For the given distribution,

Comment

a. Compute the value of mean given that of all U.S. drivers observed to be traveling at mph or more.

That is, determine the value of using the probability expression given as follows.

Step 1 of 28

Step 2 of 28

Step 3 of 28

My Textbook Solutions

Data Analysis and... 3rd Edition

Business Analytics 6th Edition

Business Analytics 5th Edition

View all solutions

Post a question Answers from our experts for your tough homework questions

Enter question

Continue to post 20 questions remaining

Statistics Chegg tutors who can help right now

Ashish Indian Institute of F… 185

Varun Université de Genève 59

Fabio Scuola Normale Su… 38

Find me a tutor

Bookmark Show all steps:Chapter 6, Problem 13P ON

  Textbook Solutions Expert Q&A Study Pack Practice NEW!

Search 

3/18/2020 Solved: It is widely known that many drivers on interstate high... | Chegg.com

https://www.chegg.com/homework-help/data-analysis-and-decision-making-with-microsoft-excel-revised-3rd-edition-chapter-6-problem-13p-solution-9… 2/7

Comment

Also,

This implies,

Comment

Perform the probability calculation using Excel Statistical function NORMDIST which

contains parameter .Thus knowing the probability, obtain the value of parameter by using Goal Seek.

Comment

First, assign any finite integer value to the mean and corresponding to that, calculate using NORMDIST which takes the parameters defined as follows,

- The value for which we want to obtain the probability,

- Mean of normal population.

- Standard Deviation of normal population,

1 – Logical value that gives cumulative probability.

Comment

Now choose the Excel statistical function NORMDIST from the formula bar ofExcel

worksheet and substitute the values of parameters. The function will generate some probability. Select the cell containing so obtained probability. Now Goal seek is used to determine the value of mean .

Comment

The steps for using Goal seek to determine the value of mean are given below

1. Go to Data, select What if analysis and choose Goal seek.

2. The dialog box for the Goal Seek will appear.

3. Set cellto the cell containing so obtained probability.

4. In the To value, input the value of the probability given that is 0.60.

Step 4 of 28

Step 5 of 28

Step 6 of 28

Step 7 of 28

Step 8 of 28

Bookmark Show all steps:Chapter 6, Problem 13P ON

  Textbook Solutions Expert Q&A Study Pack Practice NEW!

Search 

3/18/2020 Solved: It is widely known that many drivers on interstate high... | Chegg.com

https://www.chegg.com/homework-help/data-analysis-and-decision-making-with-microsoft-excel-revised-3rd-edition-chapter-6-problem-13p-solution-9… 3/7

5. By changing cell that contains the assumed mean, thereby getting the required value of mean in the same cell replacing the assumed value.

Comment

The screen shot for Goal Seek is given below.

Comment

The Excel worksheet will appear as

Comments (1)

Hence if of all U.S. drivers are observed to be traveling at mph or more then from the

above table, the value of mean speed is .

Comment

b. Determine the value of mean given that of all U.S. drivers are observed to be traveling at mph or less.

That is, to determine the value of using the probability expression given as follows.

Now perform the probability calculation using Excel Statistical function NORMDIST

which contains parameter .Thus knowing the probability, obtain the value of parameter by using Goal Seek.

Comment

First, assign any finite integer value to the mean and corresponding to that, calculate using NORMDIST which takes the parameter defined as follows

- The value for which we want to obtain the probability, that is .

Step 9 of 28

Step 10 of 28

Step 11 of 28

Step 12 of 28

Step 13 of 28

Bookmark Show all steps:Chapter 6, Problem 13P ON

  Textbook Solutions Expert Q&A Study Pack Practice NEW!

Search 

3/18/2020 Solved: It is widely known that many drivers on interstate high... | Chegg.com

https://www.chegg.com/homework-help/data-analysis-and-decision-making-with-microsoft-excel-revised-3rd-edition-chapter-6-problem-13p-solution-9… 4/7

- Mean of normal population.

- Standard Deviation of normal population.

1 – Logical value that gives cumulative probability.

Comment

Now choose the Excel statistical function NORMDIST from the formula bar of Excel

worksheet and substitute the values of parameters. The function will generate some probability. Select the cell containing the obtained probability. Now Goal seek is used to determine the value of mean .

Comment

The steps for using Goal seek to determine the value of mean are given below

1. Go to Data, select What if analysis and choose Goal seek.

2. The dialog box for the Goal Seek will appear.

3. Set cell to the cell containing so obtained probability.

4. In the To value, input the value of the probability given that is 0.25.

5. By changing cell that contains the assumed mean, thereby getting the required value of mean in the same cell replacing the assumed value.

Comment

The screen shot for Goal Seek is given below.

Comment

The Excel worksheet is given below.,

Comment

Step 14 of 28

Step 15 of 28

Step 16 of 28

Step 17 of 28

Step 18 of 28

Bookmark Show all steps:Chapter 6, Problem 13P ON

  Textbook Solutions Expert Q&A Study Pack Practice NEW!

Search 

3/18/2020 Solved: It is widely known that many drivers on interstate high... | Chegg.com

https://www.chegg.com/homework-help/data-analysis-and-decision-making-with-microsoft-excel-revised-3rd-edition-chapter-6-problem-13p-solution-9… 5/7

Hence if of all U.S. drivers are observed to be traveling at mph or less then, from the

above table we get the value of mean speed as .

Comment

c. It is given that of all U.S. drivers travel at less than mph and of all U.S. drivers travel at more than mph and corresponding to this given information, find the value of unknown mean and unknown standard deviation .

Comment

To determine the value of mean and standard deviation using the probability expression given as follows:

And

Comment

Also,

So,

This implies,

Comment

Perform the probability calculation using Excel Statistical function NORMDIST which

contains parameters and .

The arguments of the function NORMDIST are defined below.

- The value for which we want to obtain the probability.

- Mean of normal population.

- Standard Deviation of normal population.

1 – Logical value that gives cumulative probability.

Comment

Step 19 of 28

Step 20 of 28

Step 21 of 28

Step 22 of 28

Step 23 of 28

Bookmark Show all steps:Chapter 6, Problem 13P ON

  Textbook Solutions Expert Q&A Study Pack Practice NEW!

Search 

3/18/2020 Solved: It is widely known that many drivers on interstate high... | Chegg.com

https://www.chegg.com/homework-help/data-analysis-and-decision-making-with-microsoft-excel-revised-3rd-edition-chapter-6-problem-13p-solution-9… 6/7

Therefore, assign any finite integer value of your choice to the mean and standard deviation for both the given probabilities.

Now calculate the probabilities and each corresponding to these

assumed values of parameters, using the function NORMDIST .

Choose the Excel statistical function NORMDIST from the formula bar of Excel

worksheet and substitute the values of parameters. The function will generate some probability for each set of parameters.

Comment

Thus, knowing the probability, obtain the value of parameters and by using Solver add-in. Select the cell containing one of the two obtained probabilities and then from the tool menu select Solver add-in.

Comment

The dialog box for the Solver add-in will appear.

Set the cell containing probability as the target cell equal to the value of given probability, that is, for by changing the cell containing the assumed mean and assumed

standard deviation, thereby getting the required value of mean and standard deviation in the same cell replacing the assumed values.

Comment

Similarly repeat the same steps for probability to get the actual value of parameters

corresponding to this probability.

Comment

The Excel worksheet is given below.

Comment

Step 24 of 28

Step 25 of 28

Step 26 of 28

Step 27 of 28

Step 28 of 28

Bookmark Show all steps:Chapter 6, Problem 13P ON

  Textbook Solutions Expert Q&A Study Pack Practice NEW!

Search 

3/18/2020 Solved: It is widely known that many drivers on interstate high... | Chegg.com

https://www.chegg.com/homework-help/data-analysis-and-decision-making-with-microsoft-excel-revised-3rd-edition-chapter-6-problem-13p-solution-9… 7/7

Recommended solutions for you in Chapter 6

Was this solution helpful?

Hence from the above table, it is observed that for of all U.S. drivers who travel at less than mph the values of unknown mean and standard deviation are

and for of all U.S. drivers who travel at more than mph

the values of unknown mean and standard deviation are .

Comment

0 2

Chapter 6, Problem 24P

Past experience indicates that 30% of all individuals entering a certain store decide to make a purchase. Using (a) the binomial distribution and (b) the normal approximation to the binomial, find that probability that 10 or more of the 30...

See solution

ABOUT CHEGG

LEGAL & POLICIES

CHEGG PRODUCTS AND SERVICES

CHEGG NETWORK

CUSTOMER SERVICE

© 2003-2020 Chegg Inc. All rights reserved.

Bookmark Show all steps:Chapter 6, Problem 13P ON

  Textbook Solutions Expert Q&A Study Pack Practice NEW!

Search 