WorksheetFunction.Z_Test Method (Excel)
Returns the one-tailed probability-value of a z-test. For a given hypothesized population mean, Z_TEST 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.
Version Information
Version Added: Excel 2010
Syntax
expression .Z_Test(Arg1, Arg2, Arg3)
expression A variable that represents a WorksheetFunction object.
Parameters
Name |
Required/Optional |
Data Type |
Description |
|---|---|---|---|
Arg1 |
Required |
Variant |
Array is the array or range of data against which to test the hypothesized population mean. |
Arg2 |
Required |
Double |
The value to test. |
Arg3 |
Optional |
Variant |
Sigma - The population (known) standard deviation. If omitted, the sample standard deviation is used. |
Return Value
Double
Remarks
If array is empty, Z_TEST returns the #N/A error value.
Z_TEST is calculated as follows when sigma is not omitted:
.jpg "Ff835249.Z_TEST_SIGMA_ZA10391001(en-us,office.14).jpg")
or when sigma is omitted: .jpg "Ff835249.Z_TEST_ZA10391000(en-us,office.14).jpg")
where x is the sample mean AVERAGE(array); s is the sample standard deviation STDEV_S(array); and n is the number of observations in the sample COUNT(array).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) < μ 0 , Z_TEST 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 μ 0 (in either direction) than AVERAGE(array), when the underlying population mean is μ 0 : =2 * MIN(Z_TEST(array,μ 0 ,sigma), 1 - Z_TEST(array,μ 0 ,sigma)).