ZTEST - Microsoft Office Excel

Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, μ₀, 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.

To see how ZTEST can be used in a formula to compute a two-tailed probability value, see "Remarks" below.

Syntax

ZTEST(array,μ₀,sigma)

Array is the array or range of data against which to test μ₀

μ₀ is the value to test.

Sigma is the population (known) standard deviation. If omitted, the sample standard deviation is used.

Remarks

If array is empty, ZTEST returns the #N/A error value.
ZTEST is calculated as follows when sigma is not omitted:

or when sigma is omitted:

where x is the sample mean AVERAGE(array); s is the sample standard deviation STDEV(array); and 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 μ₀. From the symmetry of the Normal distribution, if AVERAGE(array) < μ₀, 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 μ₀ (in either direction) than AVERAGE(array), when the underlying population mean is μ₀:
=2 * MIN(ZTEST(array,μ₀,sigma), 1 - ZTEST(array,μ₀,sigma)).

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

1

2
3
4
5
6
7
8
9
10
11

A
Data
3
6
7
8
6
5
4
2
1
9
Formula	Description (Result)
=ZTEST(A2:A11,4)	One-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 4 (0.090574)
=2 * MIN(ZTEST(A2:A11,4), 1 - ZTEST(A2:A11,4))	Two-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 4 (0.181148)
=ZTEST(A2:A11,6)	One-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 6 (0.863043)
=2 * MIN(ZTEST(A2:A11,6), 1 - ZTEST(A2:A11,6))	Two-tailed probability-value of a z-test for the data set above, at the hypothesized population mean of 6 (0.273913)

How to copy an example

See also: