- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
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