The Excel ZTEST Function

Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Ztest-Function

Search this site:

Custom Search

ZTEST and Z.TEST

In Excel 2010, the ZTEST function has been renamed Z.TEST.

Both the ZTEST and the Z.TEST function perform the same task. Excel 2010 has kept the ZTEST function, however, for compatibility with earlier versions of Excel. This function is therefore stored within the list of compatibility functions in Excel 2010, rather than in the list of Statistical functions.

Basic Description

For a supplied hypothesized sample mean and a supplied set of values, the Excel ZTEST function calculates the one-tailed probability value of the Z-Test.

I.e. the function returns the probability that the supplied hypothesized sample mean is greater than the mean of the supplied data values.

The format of the Ztest function is :

ZTEST( array, x, [sigma] )

where the function arguments are:

array	-	The set of values against which the hypothesized sample mean is to be tested
x	-	The hypothesized sample mean
[sigma]	-	An optional argument that represents the population standard deviation, if this is known. If omitted, the calculation uses the sample standard deviation.

Two-tailed Probability

If you want to calculate the two-tailed probability value of the Z-Test, this can be done by using the Ztest function, combined with the Excel Min function, as follows:

= 2 * MIN( ZTEST(array,x,[sigma]), 1-ZTEST(array,x,[sigma]) )

Ztest Function Examples

Column A of the spreadsheet on the right contains an array of 12 data values. The mean of these values is 5.25.

Cells B1 and B2 of the example spreadsheet show the Excel Ztest function used to calculate the the one-tailed probability value of the Z-Test for two different hypothesized sample means.

For the hypothesized sample mean 5.0, the one-tailed probability value of the Z-Test is calculated by the formula:

=ZTEST( A1:A12, 5.0 )

which gives the result 0.371103279.

For the hypothesized sample mean 6.0, the one-tailed probability value of the Z-Test is calculated by the formula:

=ZTEST( A1:A12, 6.0 )

which gives the result 0.838129187.

Note that in the above two examples, the [sigma] argument is omitted from the function. Therefore, the Ztest function calculation uses the standard deviation of the supplied array.

Further information and examples of the Excel Ztest function can be found on the Microsoft Office website.