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.
expression A variable that represents a 'WorksheetFunction' object.
|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.|
If array is empty, Z_TEST returns the #N/A error value.
Z_TEST 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_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)).