Tip SAMLab Sheet #9
1
The Single-Sample Z test This Tip Sheet provides an overview of how to perform the single-sample z test in Excel, which includes calculating the test statistic, finding the appropriate critical value for comparison, and finding the pvalue of the computed z statistic. The formula for the single-sample z statistic is shown below. z= X x X
X is the mean of the sample, x is the mean of the reference population, X is the standard error of the sampling distribution created by drawing samples of size N from the reference population. The formula for the standard error is shown below. X = X N X is the standard deviation of the reference population, and N is the size of your sample. The image below shows how to calculate the standard error and the z in Excel.
These two cells show the formulas used to calculate the adjacent values These fictitious data represent the IQ scores of a sample of Psychology students. The population values are meant to reflect the general adult population, which we will assume is normally distributed.
On the next page you will see how to use Excels paste functions to find the two-tailed probability associated with the computed z statistic. You will also see how Excel can find the critical value.
SAMLab Tip Sheet #9 2 The first step in finding the two-tailed p-value associated with the computed z statistic is to click in a cell near your computed z statistic and select Function from the Insert menu (below left). Next select Statistical from the Function category list and then select NORMSDIST from the Function name list (below right). Click OK.
When the dialogue box below appears, click in the field next to Z and select the cell containing your computed z statistic. Click OK.
As you might gather from the label below, the NORMSDIST function returns the cumulative probability in the normal distribution below the referenced z statistic. In order to find the two-tailed p-value, we will need to subtract the cumulative probability from 1 to get the one-tailed probability1 and then multiply it by 2 as shown below.
SAMLab Tip Sheet #9
3
In order to find the critical value for this z-test we will use the function called NORMSINV. We will use an alpha level of .05, two-tailed. Using the same steps for inserting NORMSDIST above, insert NORMSINV in the cell below the p-value. two-tailed You should see the dialogue box below once you have selected this function.
NORMSINV() will return a z-value associated with a cumulative probability. What this means is that we need to input the cumulative probability associated with a two-tailed alpha level of .05. We know that because our computed z statistic is positive and our alpha level is two-tailed, the cumulative probability below our critical value will be .975, which is what we should enter into the Probability field as shown below (note that if we wanted the negative critical value associated with a two-tailed alpha level of .05, we would have entered .025). Once you have entered the probability, click OK. .
Label the cell next to your critical value and you should see something similar to the image below.
SAMLab Tip Sheet #9 4 Our last task will be to check our work with one of Excels other paste functions called ZTEST. Click in the cell below the critical value and insert the ZTEST() function as done previously. You should see the dialogue box at the top of the next page.
For the Array field, select the original data. For the X field, select the population mean (100 in our example), and for the Sigma field, select the population standard deviation (16 in our example). Once your dialogue box resembles the one below, click OK.
You should now have a spreadsheet that looks something like the one b...