How to Use the Z.TEST Function in Excel

Hypothesis tests are one of the major topics in the area of inferential statistics. There are multiple steps to conduct a hypothesis test and many of these require statistical calculations. Statistical software, such as Excel, can be used to perform hypothesis tests. We will see how the Excel function Z.TEST tests hypotheses about an unknown population mean. Conditions and Assumptions We begin by stating the assumptions and conditions for this type of hypothesis test. For inference about the mean we must have the following simple conditions: The sample is a simple random sample.The sample is small in size relative to the population. Typically this means that the population size is more than 20 times the size of the sample.The variable being studied is normally distributed.The population standard deviation is known.The population mean is unknown. All of these conditions are unlikely to be met in practice. However, these simple conditions and the corresponding hypothesis test are sometimes encountered early in a statistics class. After learning the process of a hypothesis test, these conditions are relaxed in order to work in a more realistic setting. Structure of the Hypothesis Test The particular hypothesis test we consider has the following form: State the null and alternative hypotheses.Calculate the test statistic, which is a z-score.Calculate the p-value by using the normal distribution. In this case the p-value is the probability of obtaining at least as extreme as the observed test statistic, assuming the null hypothesis is true.Compare the p-value with the level of significance to determine whether to reject or fail to reject the null hypothesis. We see that steps two and three are computationally intensive compared two steps one and four. The Z.TEST function will perform these calculations for us. Z.TEST Function The Z.TEST function does all of the calculations from steps two and three above. It does a majority of the number crunching for our test and returns a p-value. There are three arguments to enter into the function, each of which is separated by a comma. The following explains the three types of arguments for this function. The first argument for this function is an array of sample data. We must enter a range of cells that corresponds to the location of the sample data in our spreadsheet.The second argument is the value of  ÃŽ ¼ that we are testing in our hypotheses. So if our null hypothesis is H0:  ÃŽ ¼ 5, then we would enter a 5 for the second argument.The third argument is the value of the known population standard deviation. Excel treats this as an optional argument Notes and Warnings There are a few things that should be noted about this function: The p-value that is output from the function is one-sided. If we are conducting a two-sided test, then this value must be doubled.The one-sided p-value output from the function assumes that the sample mean is greater than the value of  ÃŽ ¼ we are testing against. If the sample mean is less than the value of the second argument, then we must subtract the output of the function from 1 to get the true p-value of our test.The final argument for the population standard deviation is optional. If this is not entered, then this value is automatically replaced in Excel’s calculations by the sample standard deviation. When this is done, theoretically a t-test should be used instead. Example We suppose that the following data are from a simple random sample of a normally distributed population of unknown mean and standard deviation of 3: 1, 2, 3, 3, 4, 4, 8, 10, 12 With a 10% level of significance we wish to test the hypothesis that the sample data are from a population with mean greater than 5. More formally, we have the following hypotheses: H0: ÃŽ ¼ 5Ha:  ÃŽ ¼ 5 We use Z.TEST in Excel to find the p-value for this hypothesis test. Enter the data into a column in Excel. Suppose this is from cell A1 to A9Into another cell enter Z.TEST(A1:A9,5,3)The result is 0.41207.Since our p-value exceeds 10%, we fail to reject the null hypothesis. The Z.TEST function can be used for lower tailed tests and two tailed tests as well. However the result is not as automatic as it was in this case. Please see here for other examples of using this function.