The Excel Z.TEST Function

Basic Description

For a supplied hypothesized sample mean and a supplied set of values, the Excel Z.TEST 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 Z.TEST function is new to Excel 2010. However, this is simply a renamed version of the ZTEST function, that is available in earlier versions of Excel.

The format of the Z.Test function is :

Z.TEST( 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 function 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 Z.Test function, combined with the Excel Min function, as follows:

= 2 * MIN( Z.TEST(array,x,[sigma]), 1-Z.TEST(array,x,[sigma]) )


Z.Test Function Examples

Z.Test Function Example Data

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 Z.Test 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:

=Z.TEST( 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:

=Z.TEST( 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 Z.Test function calculation uses the standard deviation of the supplied array.


Further information and examples of the Excel Z.Test function are provided on the Microsoft Office website.


Z.Test Function Errors

If you get an error from the Excel Z.Test Function, this is likely to be one of the following :

Common Errors
#N/A - Occurs if the supplied array is empty
#DIV/0! - Occurs if either :
- the [sigma] argument is supplied and is equal to zero
or
- the [sigma] is not supplied and the standard deviation of the supplied array is zero
or
- the supplied array contains just one value
#VALUE! - Occurs if either the supplied x or the supplied [sigma] is non-numeric
Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net