Practice free

ZTEST function

ZTEST returns the one-tailed p-value of a z-test, testing whether a sample mean is above a hypothesized value. It is a compatibility function, so use Z.TEST in new workbooks.

Example

Read it as a sentence: run a one-tailed z-test on A1:A8 against a hypothesized mean of 4, which gives 0.092938366.

Practice this in the app

Syntax

ZTEST(array, x, [sigma])

Arguments in [square brackets] are optional.

array (required)
Sample data.
x (required)
Hypothesised mean.
sigma (optional)
Known population standard deviation; if omitted, the sample's is used.

Returns: The one-tailed p-value of a z-test.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=ZTEST({3,6,7,8,6,5,4,2,1,9},4) 0.090574197 Array constant
=ZTEST(Data!A1:A8,4) 0.092938366 Range, no sigma
=ZTEST(Data!A1:A8,4,2) 0.078649604 With sigma
=ZTEST(Data!B1:B8,4) 0.005082601 Mixed range
=ZTEST({5},4) #DIV/0! Single value no sigma
=ZTEST(Data!A1:A8,4,0) #NUM! Sigma 0
=ZTEST(Data!D1:D5,4) #N/A Empty range
=ZTEST(Data!A1:A8,"abc") #VALUE! Text x

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • Superseded by Z.TEST; use that in new workbooks.
  • One-tailed; double it for a two-tailed test.

Where it works

Excel
ZTEST is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
Google Sheets
Same name, same behaviour for everyday use.

Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .