A Guide to Using Excel for Handling Data

Conducting a Z-Test: Two Sample Means

All we have to do is collect the data on the two samples, request a z-test analysis from the Excel statistics options, and examine the table of the results. The following notes describe the procedure.

1. First enter in the data for your two samples, one in Column A and the other in Column B. The number of entries in each column does not have to be the same, but there must be more than 30 entries in each column.
   
   
2. Then, using the Descriptive Statistics tool from the Data Analysis option (on the Tools Menu), generate the table of Descriptive Statistics for each sample. Note down the figure for the Variance of each sample.
   
   
3. Then from the Data Analysis option, select the last item: z-test: Two Sample Means. When you get the dialogue box, in the Variable 1 Range box, indicate the range of the first sample in Column A (e.g., $A$1:$A$35). In Variable 2 Range, indicate the range of the second sample in Column B (e.g., $B$1:$B$48). In the Hypothesized Mean Difference Box, enter the figure 0. Since the Null Hypothesis, which we are attempting to refute, says that both samples come from the same population, we are hypothesizing that the difference between the means for the two populations is 0.
   
   
4. In the Variable 1 Variance (known) box enter the figure for the Variance for the sample in the A Column (you should have noted this down earlier, but you can find this figure in the Descriptive Statistics box generated in the second step described above). In the Variable 2 Variance (known) box enter the corresponding figure for the Variance of the second sample.
   
   
5. In the Alpha box the number 0.05 should already appear. Leave this alone for the moment (if it is empty or shows a number different from 0.05, then enter the number 0.05). The Alpha figure indicates the Confidence Level for this test. A figure of 0.05 states that you want to be 95 per cent certain of the result or, in other words, that you want the probability of being wrong to be .05 or lower.
   
   
6. In the Output Range box, type the number of the cell where you want the Output Table to appear (or alternatively, with the line active in the Output Range box, click the mouse on an empty cell). The Output Range table will take up three columns and twelve horizontal rows.
   
   
7. Then click on OK. After a couple of seconds, a table should appear in the place designated by the Output entry. This table has the heading: z-Test: Two Samples for Means. You will need to widen the left hand column of the table in order to read the names of the items. In the table there are figures for the following items: Mean, Known Variance, Observations, Hypothesized Mean Difference, z, P(Z<=z) one-tail, z Critical one-tail, P(Z<=z) two-tail, z Critical two tail.
   
   

The Mean figures gives the arithmetical average for each sample. It should be the same as the figure for the Mean in your Descriptive Statistics chart you generated earlier. The Known Variance similarly gives the Variance for each sample and corresponds to the Variance figures generated earlier (these are the figures you entered into the z-Test dialogue box). The Observations is the number of items in each sample. The Hypothesized Mean Difference should be 0 (the figure you entered in the z-Test dialogue box earlier).

The z figure indicates in standard deviation units how far from the mean the figure for the difference between your two samples is located. Remember that the normal curve for all the differences between all the possible pairs of samples from the population has a mean of 0. Your two samples did not have the same mean; thus they fall away from the mean in the normal distribution. The z figure tells you how far away the difference falls.

Following the z figure there are four lines, two concerning one-tail and two concerning two-tail testing. You will use one or the other of these pairs of figures, not both. The one you use will depend upon the nature of your Alternative Hypothesis.

If your Alternative Hypothesis makes a claim about a particular difference between the two populations, then you need the one-tail figures. For instance, an Alternative Claim like "Women drink more alcohol than men" or "Lecturer 1 gives higher marks than Lecturer 2" or "People who smoke more than twenty cigarettes a day have more heart attacks than people who do not smoke" then you will be needing the one-tail figures. Since your Alternative Hypothesis argues that one of the populations will have a higher value than the other, then you are interested only in one end of the distribution curve.

However, if your Alternative Hypothesis simply asserts that there will be a significant difference between the populations (without saying which will be higher or lower), then you need the two-tail figures. For example, you will need a two-tailed test for any Alternative Hypothesis like the following: "There is a difference in the amounts of alcohol that men and women drink," "Lecturer 1 and Lecturer 2 mark at different standards," Note that the interpretation of the figures is the same, no matter which of the two you are using, but the figures will be different.

The P(Z<=z) figure indicates the probability that the two populations are the same. So a figure here of, say, 0.02 would indicate that the probability of the Null Hypothesis being correct (that there is no difference between the populations) is .02 or 2 per cent. Put another way, there is a .98 probability that the Null Hypothesis is not correct (or p = .98).

Whether or not the figure for P(Z<=z) enables you to confirm or dismiss the Null Hypothesis will depend upon the confidence level you set. If your level is .05, then a figure of .02 (smaller than the specification) indicates that the Null Hypothesis should be rejected and the Alternative Hypothesis affirmed. However, if the Confidence Level you have set is .01, then a result for P(Z<=z) of .02 (which is higher than the specification) enables you to affirm the Null Hypothesis.

Another way quickly of determining whether to affirm or reject the Null Hypothesis is to examine the z Critical figure. This number indicates the value beyond which the z figure is too high or too low for one to accept the Null Hypothesis. So to determine whether or not one should affirm or deny the Null Hypothesis, simply compare the z figure with the z-Critical figure. If the z figure is less than the z-Critical figure, you affirm the Null Hypothesis; if the z-figure is greater than the z-Critical figure, you reject it.

If you change the Confidence Level in a z-test of this sort, you will notice that the z-Critical values will change. For instance, if you go back and start the z-test again, but this time in the Dialogue Box enter a value for Alpha of 0.01 (rather than 0.05), then you are demanding a Confidence Level of 99 per cent or, alternatively, you want a result in which there is only 1 per cent chance (p = 0.01) of your being wrong.

If you do that and generate a second z-test: Two Sample for Mean table, you will notice that all the values in that table are the same as for the first table, except for the z-Critical values, which have increased. What that means is that if you want to be more confident of your result, you have to widen the interval within which you judge results to be acceptable.

Note that whenever you state a conclusion to a z-test, you must indicate the confidence level you used. As we have discussed in the text, a Null Hypothesis which you reject at a .05 value for Alpha (a confidence of 95 per cent), you may have to accept at a .01 value for Alpha (a confidence of 99 per cent).

[Top]