Compatibility - ZTEST Function

The ZTEST function replaces the Z.TEST function in Excel 2010.

Description

The function returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, $\mu_0$, ZTEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) - that is, the observed sample mean.

Syntax

ZTEST (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

  • ZTEST is calculated as follows when sigma is not omitted −

    $$ZTEST(array,\mu_0)=1-NORMDIST((\bar{x}-\mu_0)/(sigma/\sqrt{n}))$$

    Alternatively, when sigma is omitted −

    $$ZTEST(array,\mu_0)=1-NORMDIST((\bar{x}-\mu_0)/(s/\sqrt{n}))$$

    Where,

    x is the sample mean AVERAGE(array),

    s is the sample standard deviation STDEV(array).

    n is the number of observations in the sample COUNT(array).

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

  • The following Excel formula can be used to calculate the two-tailed probability that the sample mean would be further from $mu_0$ (in either direction) than AVERAGE(array), when the underlying population mean is $mu_0$ −

    =2 * MIN (ZTEST (array,$mu_0$,sigma), 1 - ZTEST(array,$mu_0$,sigma))

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

Example

ZTEST Function
advanced_excel_compatibility_functions.htm
Advertisements