MATH 2510 Activity 3 | Complete Solution

MATH 2510 - Introduction to Statistics 1 Activity 3

This activity again requires that you complete some computations in Excel. However, in this activity YOU
will be both the designer and programmer of the Excel datasheet. Further, you will only need to submit an
Microsoft Excel workbook (.xlsx) file. No additional written responses are required.
In summary, you are required to build two separate Hypothesis Test Calculators in Excel. Precisely what
Excel functions you use, what intermediate calculations you may decide to perform, and what layout you
choose to collect information and present results is up to you. However, in order to be functional, your
calculator must make sense to a user other than you AND your calculator must be robust to handle any data
for any kind of hypothesis test for a single population mean.
Your Excel workbook will be graded based on two considerations: “Is your calculator design user-friendly
enough to be understood by the instructor?” and “Does your calculator generate the correct results when
different values are entered into the calculator?” Each calculator will be subjected to three sets of test data.
You are welcome to discuss this activity with other students, but please make sure that you have designed
your own calculator. Exact duplicates of Excel workbooks will be considered a violation of the Honor Code.
Submit the completed Excel workbook to the D2L Dropbox for this activity no later than 11pm (MT) on
Friday, July 21. Documents of any other format or submitted in any place other than the D2L Dropbox will
not be accepted for grading.
1. Hypothesis Test for a Population Mean (µ) when the Population Standard Deviation (σ)
Is Known
For this calculator, the user will need to enter the following.
• The null hypothesis population mean value
• The known population standard deviation value
• The alpha value of the hypothesis test
• The mean of the random sample selected
• The size of the random sample selected
2. Hypothesis Test for a Population Mean (µ) when the Population Standard Deviation (σ)
Is UNKnown
For this calculator, the user will need to enter the following.
• The null hypothesis population mean value
• The alpha value of the hypothesis test
• The mean of the random sample selected
• The standard deviation of the random sample selected
• The size of the random sample selected
3. In both cases, the results must include the following.
• The value of the standardized test statistic
• The P-value for the test statistic
• The critical values for the rejection region
• The result of “Reject”- or “Fail to reject”- the null hypothesis
If you would like some further suggestions and hints, please read on. Otherwise, BEGIN.
CU Boulder MATH 2510 - Introduction to Statistics 2
SOME TIPS AND SUGGESTIONS
1. Note that although there are built-in function in Excel called Z.TEST and T.TEST, they refer to
arrays of data. When only the sample statistics are provide (and not the sample data itself), then
these functions cannot be used directly.
2. The standardized test statistic can be calculated by arithmetic formula from the book. Once you
have the values of ¯x, µ, σ or s, and n in your Excel datasheet, simply use cell references and enter the
relevant arithmetic formula.
3. The P-value can be computed using the NORM.DIST or the T.DIST function once the standardized
test statistic has been determined. See below for more information on these functions.
4. The critical value(s) can be computed using the NORM.INV or the T.INV function. See below for
more information on these functions.
5. The result of “Reject the null hypothesis” or “Fail to reject the null hypothesis” can be programmed
with the IF function. Once the P-value is determined, you can program a formula to say “IF P ≤
α, THEN reject the null hypothesis, ELSE fail to reject the null hypothesis”. See below for more
information on the IF function.
6. If you are struggling to come up with design ideas:
• Here is a snapshot of a calculator when the direction of the tail of the test is actually entered (as
“Left”, “Right”, or “Two”) and that information is referenced in the critical value(s) and P-value
computations. So, the user must first interpret what kind of hypothesis test it is.
(Note that this data is from Example 4 on page 366.)
• Here is a snapshot of a calculator where three different results (one for left-tailed, one for righttailed,
and one for two-tailed) are generated. In order to use the results of the calculator, the user
must then know which result is relevant.
(Note that this data is from Example 5 on page 381.)
CU Boulder MATH 2510 - Introduction to Statistics 3
NORM.DIST function
• The syntax for the function is =NORM.DIST(x, mean, standard dev, cumulative).
• x: The data value for which left-tail area is being computed, which in this case is the standardized test statistic,
z.
• mean: The mean of the normal distribution, which in this case is 0.
• standard dev: The standard deviation of the normal distribution, which in this case is 1.
• cumulative: A logical flag to indicate whether the function should return the cumulative area under the normal
distribution curve to the left of x or just the height of the normal distribution curve at x. Because you are
looking for area, this argument is always “TRUE”, or equivalently, 1. Note that it is ALWAYS a left-tail area
from x. So, if you are looking for a right-tail or two-tail result, then some adjustments must be made.
T.DIST function
• The syntax for the function is =T.DIST(x, deg freedom, cumulative).
• x: The data value for which left-tail area is being computed, which in this case is the standardized test statistic,
t.
• deg freedom: The degrees of freedom, which in this case is the sample size minus 1.
• cumulative: A logical flag to indicate whether the function should return the cumulative area under the normal
distribution curve to the left of x or just the height of the normal distribution curve at x. Because you are
looking for area, this argument is always “TRUE”, or equivalently, 1. Note that it is ALWAYS a left-tail
area from x. For right-tail or two-tail results, some adjustments must be made, or look into T.DIST.RT and
T.DIST.2T.
NORM.INV function
• The syntax for this function is =NORM.INV(probability, mean, standard dev).
• probability: The alpha value for a left-tail test. Note that this function only uses left-tail area, so for a right-tail
or two-tail test some adjustments must be made.
• mean: The mean of the normal distribution, which in this case is 0.
• standard dev: The standard deviation of the normal distribution, which in this case is 1.
T.INV function
• The syntax for this function is =T.INV(probability, deg freedom).
• probability: The alpha value for a left-tail test. Note that this function only uses left-tail area, so for a right-tail
or two-tail test some adjustments must be made.
• deg freedom: The degrees of freedom, which in this case is the sample size minus 1.
IF function
• The syntax for this function is =IF(logical test, value if true, value if false).
• logical test: Any value or expression that can be evaluated as TRUE or FALSE. For example, such an expression
could be “P < α”. If the P-value is less than α, then the value of the expression is TRUE, otherwise it is
FALSE.
• value if true: The value returned in the cell when the value of the logical test is TRUE. For example, such a
value could be “Reject the null hypothesis”.
• value if false: The value returned in the cell when the value of the logical test is FALSE. For example, such a
value could be “Fail to reject the null hypothesis”.