Question

Does anyone know how to do it on EXCEL?

Does anyone know how to do it on EXCEL?

Does anyone know how to do it on EXCEL?

Does anyone know how to do it on EXCEL? Anyone know how to do it on EXCEL?

The Statistical Abstract of the United States published by the U.S. Census Bureau reports that the average annual consumption of fresh fruit per person is 99.9 pounds. The standard deviation of fresh fruit consumption is about 30 pounds. Suppose a researcher took a random sample of 38 people and had them keep a record of the fresh fruit they ate for one year.

a) What is the probability that the sample average would be less than 90 pounds?

b) What is the probability that the sample average would be between 98 and 105 pounds?

c) What is the probability that the sample average would be less than 112 pounds?

d) What is the probability that the sample average would be more than 93 pounds?

Answer 1

We can use NORM.DIST function in excel to answer all the four parts.

NORM.DIST(x, mean, standard_dev ,cumulative)

NORM.DIST gives the probability that a number falls at or below a given value of a normal distribution.

• x -- The value you want to test.
• mean -- The average value of the distribution.
• standard_dev -- The standard deviation of the distribution.
• cumulative -- If FALSE or zero, returns the probability that x will occur; if TRUE or non-zero, returns the probability that the value will be less than or equal to x.

Answer a)

In this case we have to find P(x < 90)

x = 90

Mean = 99.9

Standard Dev. (For Sampling Distribution) = 30/SQRT(38)

Cumulative = TRUE (As we have to find probability that the sample average would be less than 90 pounds)

You will have to type following functions in excel to get answer:

=NORM.DIST(90, 99.9, 30/SQRT(38),TRUE)

The value obtained = 0.0210

Answer b)

In this case we have to find P(98 < x < 105)

P(98 < x < 105) = P(x<105) - P(x<98)

x = 98 and 105

Mean = 99.9

Standard Dev. (For Sampling Distribution) = 30/SQRT(38)

Cumulative = TRUE

You will have to type following functions in excel to get answer:

=NORM.DIST(105, 99.9, 30/SQRT(38),TRUE) - NORM.DIST(98, 99.9, 30/SQRT(38),TRUE)

The value obtained = 0.5046

Answer c)

In this case we have to find P(x < 112)

x = 112

Mean = 99.9

Standard Dev. (For Sampling Distribution) = 30/SQRT(38)

Cumulative = TRUE

You will have to type following functions in excel to get answer:

=NORM.DIST(112, 99.9, 30/SQRT(38),TRUE)

The value obtained = 0.9935

Answer d)

In this case we have to find P(x > 93)

x = 93

Mean = 99.9

Standard Dev. (For Sampling Distribution) = 30/SQRT(38)

Cumulative = TRUE (As we have to find probability that the sample average would be less than 90 pounds)

You will have to type following functions in excel to get answer:

=1 - NORM.DIST(93, 99.9, 30/SQRT(38),TRUE)

The value obtained = 0.9219

Following is the screenshot of excel sheet: