Advanced Excel Statistical - Z.TEST Function



Description

The Z.TEST function returns the one-tailed P-value of a z-test. For a given hypothesized population mean, x, Z.TEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) i.e. the observed sample mean.

Syntax

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

Arguments

Argument Description Required/ Optional
Array The array or range of data against which to test x. Required
x The value to test Required
Sigma

The population (known) standard deviation.

If omitted, the sample standard deviation is used.

Optional

Notes

  • Z.TEST is calculated as follows −

    • when sigma is not omitted − Z.TEST( array,x,sigma ) = 1- Norm.S.Dist ((Average(array)- x) / (sigma/$\sqrt{n}$),TRUE).

    • when sigma is omitted − 1- Norm.S.Dist ((Average(array)- x) / (STDEV(array)/$\sqrt{n}$),TRUE).

where x is the sample mean AVERAGE(array), and n is COUNT(array).

  • If array is empty, Z.TEST returns the #N/A error value.

  • Z.TEST represents the probability that the sample mean would be greater than the observed value AVERAGE(array), when the underlying population mean is μ0. From the symmetry of the Normal distribution, if AVERAGE(array) < x, Z.TEST will return a value greater than 0.5

  • You can calculate the two-tailed probability that the sample mean would be further from x (in either direction) than AVERAGE(array), when the underlying population mean is x −

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

  • If the supplied array is empty, Z.TEST returns the #N/A error value.

  • If the sigma argument is supplied and is equal to zero, Z.TEST returns the #NUM! error value.

  • If the sigma argument is not supplied and the standard deviation of the supplied array is zero, Z.TEST returns the #DIV/0! error value.

  • If the supplied array contains just one value, Z.TEST returns the #DIV/0! error value.

  • If either the supplied x or the supplied sigma is non-numeric, Z.TEST returns the #VALUE! error value.

Applicability

Excel 2010, Excel 2013, Excel 2016

Example

Z.Test Function
advanced_excel_statistical_functions.htm
Advertisements