Documentation/Calc Functions/Z.TEST
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
Z.TEST
Category:
Statistical Analysis
Summary:
Performs a single-sample Z-test to help determine whether the mean of a given sample is significantly different from the specified estimate for the population mean. A Z-test is appropriate when the population is approximately normally distributed with a known standard deviation, with samples comprising more than 30 values.
Z.TEST returns a value that represents the probability (p-value) that another random sample of the same size has a mean greater than or equal to that of the supplied sample. This value is a one-sided cumulative probability that corresponds to the area under the standard normal curve to the right of the z-score calculated from the input arguments.
Calc provides a separate data analysis tool to perform Z-tests of two data samples and this can be accessed via
▸ ▸ .Syntax:
Z.TEST(Data; mu[; sigma])
Returns:
Returns a real number in the range [0, 1], which is the cumulative probability that corresponds to the area under the standard normal curve to the right of the z-score calculated from the input arguments.
Arguments:
Data is an array of real numbers that is a random sample from a population. This argument can take the form of an explicit cell range, the name of a named range, the name of a database range, or an inline constant array.
mu is a real number, or a reference to a cell containing that number, which is the known or estimated population mean.
sigma is a positive real number, or a reference to a cell containing that number, which is the known population standard deviation. If omitted, the standard deviation of the values in Data is calculated and used instead.
- If either mu or sigma is non-numeric, then Z.TEST reports a #VALUE! error.
- Z.TEST ignores any cell in Data that contains text or is empty.
- If Data contains less than two numeric values, then Z.TEST reports a #DIV/0! error.
- If sigma is negative or zero, then Z.TEST reports an invalid argument error (Err:502).
Additional details:
- Calc's Z.TEST and ZTEST functions perform identical calculations. The requirements for ZTEST are specified in ODF 1.2; Z.TEST is provided for interoperability with Microsoft Excel.
- If a value is provided for the sigma argument, Z.TEST calculates a z-score ([math]\displaystyle{ z }[/math]) as follows:
- [math]\displaystyle{ z~=~\left(\frac{\bar{x}-\mu}{\sigma}\right)\sqrt{n} }[/math]
- where
- [math]\displaystyle{ \bar{x} }[/math] is the mean of the values in the Data argument.
- [math]\displaystyle{ \mu }[/math] is the estimated population mean passed in the mu argument.
- [math]\displaystyle{ \sigma }[/math] is the known population standard deviation passed in the sigma argument.
- [math]\displaystyle{ n }[/math] is the count of numeric values in the Data argument.
- Appropriate adjustments to this formula are made if the sigma argument is omitted and Z.TEST calculates the standard deviation from the sample values in the Data argument.
- For the calculated z-score, the returned probability ([math]\displaystyle{ p }[/math]) is calculated as follows:
- [math]\displaystyle{ p~=~\frac{1}{\sqrt{2\pi}}\int_{z}^{\infty } e^{- \frac{x^2}{2}} ~dx }[/math]
- The name space for Z.TEST is
COM.MICROSOFT.Z.TEST
. - For more information on Z-tests, visit Wikipedia's Z-test page.
Examples:
Note that the sample data in the following examples is unrealistic and provided for illustrative purposes only – a typical sample for a Z-test should comprise at least 30 values.
Formula | Description | Returns |
---|---|---|
=Z.TEST(A1:A7; B1) where cells A1:A7 contain the values 5, 5.9, 4.1, 7.3, 2.8, 1.2, and 6 respectively. Cell B1 contains the value 6. | Here the sigma argument is omitted and so the function calculates the z-score and p-value by estimating the standard deviation using the seven sample values provided. | 0.960472999851546 |
=Z.TEST(A1:A7; 6; 3) where cells A1:A7 contain the same values as in the previous example. | Here the function calculates the z-score and the p-value using the population standard deviation provided. | 0.889162282008845 |
=Z.TEST({5, -5.9, 4.1, -7.3, 2.8, 1.2, 0}; -2.4; 4.5) | Here the function calculates the z-score and p-value based on the sample values specified in a constant inline array. | 0.0803579087762828 |
Related LibreOffice functions:
ODF standard:
None
Related (or similar) Excel functions:
Z.TEST