How to use the ZTEST function in Excel

Within inferential statistics it is necessary to perform what is known as hypothesis testing; For this, multiple data management programs are available such as SPSS, SAS, SVIVO, as well as the widely used Microsoft Excel. In Excel, the ZTEST function provides the probability that the sample mean is greater than the average of the observations in the data set.

ZTEST Function Syntax

The function syntax must contain the following:

• Matrix: refers to the range of data with which x has to be verified.
• x: refers to the value to check.
• Sigma: This value is optional, it refers to the standard deviation of the population. if not set, the function will use the sample standard deviation.
• The syntax is: TEST.ZN(matrix,x,[sigma])

Example of use

With the following data we can see how the ZTEST function behaves. With a simple random sample from a normally distributed population, mean unknown, and standard deviation of 3.

• Data: 1, 2, 3, 3, 4, 4, 8, 10, 12.

With a significance of 10%, we then proceed to test the hypothesis that the sample data come from a population with a mean greater than 5. With this reasoning, the following hypotheses are presented:

• H  ₀  : μ = 5
• H  _a  : μ> 5

And using the ZTEST function, the p-value is found as follows:

The data is entered in an Excel column (from A1 to A9), in another cell TEST.Z is entered (A1: A9,5,3). This will give us 0.41207 as a result. Since p exceeds 10%, we do not reject the null hypothesis.

important remarks

If the array argument is empty, the function returns the #N/A error.

When sigma is not omitted, the function is calculated as follows: TEST.Z(  matrix,x,sigma  ) = 1-  RDist.NORM((Average(matrix)-  x) / (sigma/√n),TRUE) .

The ZTEST function represents the probability that the mean is greater than the observed value.

To calculate the two-tailed probability that the sample is further from x than from average than from average with the following formula:

=2 * MIN(TEST.ZN(matrix,x,sigma); 1 – TEST.ZN(matrix,x,sigma)).

Examples of the formulas

With the following data in a column you will get different results according to the formulas given above:

Data: 3, 6, 7, 8, 6, 5, 4, 2, 1, 9. (from cell A1 to A11)

• Formula: =TEST.ZN(A2:A11,4) Result: 0.090574
• Formula: =2 * MIN(TEST.ZN(A2:A11,4); 1 – TEST.ZN(A2:A11,4)) Result: 0.181148
• Formula: =TEST.ZN(A2:A11,6) Result: 0.863043
• Formula: =2 * MIN(TEST.ZN(A2:A11,6); 1 – TEST.ZN(A2:A11,6)) Result: 0.273913

References

Microsoft (s/f). ZTEST function. Available at: https://support.microsoft.com/es-es/office/funci%C3%B3n-prueba-z-d633d5a3-2031-4614-a016-92180ad82bee

Parrado, F. (2016). Function TEST.Z Excel 2013. Available at: https://youtu.be/Yf8OpYnXJOA