Z.TEST function
Z.TEST returns the one-tailed p-value for a z-test, testing whether a sample mean is above a hypothesized value. Double it for a two-tailed result, or use T.TEST for small samples.
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.
Excel shows 0.092938366
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
Z.TEST(array, x, [sigma])
Arguments in [square brackets] are optional.
-
array(required) - The sample data.
-
x(required) - The hypothesised mean to test against.
-
sigma(optional) - Known population standard deviation; if omitted the sample standard deviation is used.
Returns: The one-tailed p-value for the z-test.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=Z.TEST({3,6,7,8,6,5,4,2,1,9},4) | 0.090574197 | Array constant |
=Z.TEST(Data!A1:A8,4) | 0.092938366 | Range, no sigma |
=Z.TEST(Data!A1:A8,4,2) | 0.078649604 | With sigma |
=Z.TEST(Data!B1:B8,4) | 0.005082601 | Mixed range |
=Z.TEST({5},4) | #DIV/0! | Single value no sigma |
=Z.TEST(Data!A1:A8,4,0) | #NUM! | Sigma 0 |
=Z.TEST(Data!D1:D5,4) | #N/A | Empty range |
=Z.TEST(Data!A1:A8,"abc") | #VALUE! | Text x |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
D
1
2
10
2
4
20
3
4
TRUE
4
4
5
5
abc
6
5
30
7
7
FALSE
8
9
40
Data
Watch out for
- It is one-tailed; double it (or use 2*MIN(p,1-p)) for two-tailed.
- Prefer T.TEST for small samples when sigma is unknown.
Where it works
- Excel
- Z.TEST arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .