The Z.TEST function returns the one-tailed P-value of a z-test for a supplied hypothesized sample mean and a supplied set of values.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.
  • The Z.TEST function replaces the ZTEST function included in earlier versions of Excel.

Syntax

=Z.TEST(array,x,[sigma])

Arguments

Argument Description
array The array or range of data against which to test x
x The hypothesized sample mean
[sigma] Optional. The population (known) standard deviation

  If [sigma] is omitted, the sample standard deviation is used

Examples

  A B C D
1 Data      
2 5      
3 1      
4 7      
5 5      
6 3      
7 5      
8 2      
9 3      
10 4      
11 6      
12        
13 Formula Result Notes
14 =Z.TEST(A2:A11,4) 0.030238192 One-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 3
15 =2*MIN(Z.TEST(A2:A11,4),1-Z.TEST(A2:A11,4)) 0.060476384 Two-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 3
16 =Z.TEST(A2:A11,6) 0.999407813 One-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 6

Note: You can combine the Z.TEST function and the MIN function to calculate the two-tailed probability value of the Z-test, i.e. =2*MIN(Z.TEST(array,x,[sigma]),1-Z.TEST(array,x,[sigma])).

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if either the supplied x or the supplied [sigma] is non-numeric
#DIV/0! Occurs if either:

  the [sigma] argument is supplied and is equal to zero
  the [sigma] is not supplied and the standard deviation of the supplied array is zero
   supplied array contains just one value
#N/A Occurs if the supplied array is empty