If you know the variance or standard deviation of the underlying population, you can calculate z-test values in Excel by using the Data Analysis add-in. You might typically work with z-test values to calculate confidence levels and confidence intervals for normally distributed data. To do this, take these steps:
To select the z-test tool, click the Data tab’s Data Analysis command button.
When Excel displays the Data Analysis dialog box, select the z-Test: Two Sample for Means tool and then click OK.
Excel then displays the z-Test: Two Sample for Means dialog box.
In the Variable 1 Range and Variable 2 Range text boxes, identify the sample values by telling Excel in what worksheet ranges you've stored the two samples.
You can enter a range address into the text boxes here or you can click in the text box and then select a range by clicking and dragging. If the first cell in the variable range holds a label and you include the label in your range selection, select the Labels check box.
Use the Hypothesized Mean Difference text box to indicate whether you hypothesize that the means are equal.
If you think that the means of the samples are equal, enter 0 (zero) into this text box or leave the text box empty. If you hypothesize that the means are not equal, enter the difference.
Use the Variable 1 Variance (Known) and Variable 2 Variance (Known) text boxes to provide the population variance for the first and second samples.
In the Alpha text box, state the confidence level for your z-test calculation.
The confidence level is between 0 and 1. By default, the confidence level equals 0.05 (equivalent to a 5-percent confidence level).
In the Output Options section, indicate where the z-test tool results should be stored.
To place the z-test results into a range in the existing worksheet, select the Output Range radio button and then identify the range address in the Output Range text box. If you want to place the z-test results someplace else, use one of the other options.
Click OK.
Excel calculates the z-test results. Here’s the z-test results for a Two Sample for Means test. The z-test results show the mean for each of the data sets, the variance, the number of observations, the hypothesized mean difference, the z-value, and the probability values for one-tail and two-tail tests.
