Z test for one mean using Microsoft Excel

Reproduced with permission from
Gerry's Handbook of Statistical Analysis for Non-math Types.

This page assumes you are familiar with the basics of hypothesis testing.

We use this test when:

1. The scale of the data is interval or ratio.
2. The sample size is at least 30 (so we can take advantage of the Central Limit Theorem).
3. Or if the sample size is under 30, the data is normally distributed and the population standard deviation is known.

Example: The programmers at a software company averaged 2.2 coding mistakes (AKA bugs) per 1000 lines of code with a standard deviation of 1.48. Management hired a training consultant to see if the average number of bugs could be reduced. Six months after the training program, the company sampled 40 sets of 1000 lines of code, 2 from each of its 20 programmers. These were the number of bugs in each set:

2	3	2	3	5	2	1	2	1	0
1	3	1	0	2	0	1	2	1	3
2	2	2	3	6	2	1	1	2	2
3	1	2	4	1	0	0	1	1	0

Was the training program successful in reducing the average number of bugs? Test at a 1% level of significance.

This is a left-tail test because we want to see if the average number of bugs is less than 2.2.

First we put the data in column A, from A1 through A40.

From Gerry' Stats Tools, we choose Tests for one mean/median and then Z/t tests to bring up the dialogue box.

• The input range is a1:a40.
• We leave the population size box blank.
• For the population standard deviation, we put 1.48.
• We set the level of significance at 0.01.
• We choose left tail for the type of test.
• The hypothesized mean is 2.2.
• We choose b1 for the output range.

This is the output:

Z test for one mean
Ho: population mean is not less than 2.2
Ha: population mean is less than 2.2
Reject Ho if test statistic < -2.326
Test statistic = -1.816
P-value = 0.035
Do not reject Ho
Conclude population mean is not less than 2.2
99% confidence interval: 1.172 to 2.378

Since this is a left-tail test and the level of significance is 1%, we reject the null hypothesis if the test statistic is less than -2.326. Since the test statistic has a value of -1.816, we see that we do not reject the null hypothesis and conclude that the mean is not less than 2.2. In terms of the original question, this means that the training program was not successful and that the money would have been better spent on buying Twinkies for the staff.

Would you like Gerry's Stats Tools? Go to the download page.

Papers Index | Home