Z.TEST Function (LibreOffice Calc)
The Z.TEST function in LibreOffice Calc performs a one-sample z-test and returns the probability that the sample mean is greater than the hypothesized population mean. This guide explains syntax, interpretation, examples, errors, and best practices.
This guide provides syntax, interpretation rules, examples, common errors, and best practices, making it one of the most complete Z.TEST references available for LibreOffice Calc.
What the Z.TEST Function Does
- Performs a one-sample z-test
- Returns the one-tailed probability that the sample mean > hypothesized mean
- Assumes known or approximated population standard deviation
- Useful for hypothesis testing and probability evaluation
- Works across sheets
Z.TEST answers the question:
“If the true mean were μ₀, what is the probability of observing a sample mean this large or larger?”
Syntax
Z.TEST(array; x; sigma)
Where:
array— sample datax— hypothesized population mean (μ₀)sigma— (optional) population standard deviation- If omitted, Calc uses STDEV.S(array) as an estimate
For two-tailed tests, multiply the result by 2.
Interpretation of Z.TEST Results
| Z.TEST Output | Meaning |
|---|---|
| Near 1 | Sample mean is much larger than μ₀ |
| ~0.5 | Sample mean close to μ₀ |
| Near 0 | Sample mean is much smaller than μ₀ |
| < 0.05 | Statistically significant (one-tailed) |
| < 0.025 | Statistically significant (two-tailed) |
Basic Examples
One-sample z-test with known sigma
=Z.TEST(A1:A30; 50; 10)
Z-test using sample SD (sigma omitted)
=Z.TEST(A1:A30; 50)
Z-test across sheets
=Z.TEST(Sheet1.A1:A50; Sheet2.B1)
Two-tailed z-test
=2 * Z.TEST(A1:A30; 50)
Advanced Examples
Z-test ignoring errors
=Z.TEST(IF(ISNUMBER(A1:A100); A1:A100); 50)
(Confirm with Ctrl+Shift+Enter in older Calc.)
Z-test using filtered (visible) data only
Use SUBTOTAL helper column to filter values before passing to Z.TEST.
Z-test after removing outliers
=Z.TEST(FILTER(A1:A100; A1:A100<1000); 50)
Z-test for standardized data
=Z.TEST(STANDARDIZE(A1:A30; Mean; SD); 0)
Z-test for quality control (process mean)
=Z.TEST(Measurements; TargetValue; KnownSigma)
Z-test for large-sample approximations
=Z.TEST(A1:A500; HypothesizedMean)
How Z.TEST Calculates the Probability
- Compute sample mean:
[ \bar{x} = \text{AVERAGE}(array) ]
- Compute standard deviation:
- If sigma provided: ( \sigma )
- Else: ( s = \text{STDEV.S}(array) )
- Compute z-score:
[ z = \frac{\bar{x} - \mu_0}{\sigma / \sqrt{n}} ]
- Compute one-tailed probability:
[ p = 1 - \Phi(z) ]
Where ( \Phi ) is the standard normal CDF.
Common Errors and Fixes
Err:502 — Invalid argument
Occurs when:
- Array contains no numeric values
- Sigma ≤ 0
- x is non-numeric
Err:504 — Parameter error
Occurs when:
- Semicolons are incorrect
- Range references malformed
Z.TEST returns unexpected value
Possible causes:
- Wrong sigma (population vs sample)
- Two-tailed test not adjusted
- Outliers affecting mean and SD
- Non-normal data (z-test assumes approximate normality)
Best Practices
- Use Z.TEST when population SD is known or sample size is large
- Use T.TEST when population SD is unknown and sample size is small
- Remove outliers before testing
- Use named ranges for cleaner formulas
- Multiply by 2 for two-tailed tests
- Use STANDARDIZE for manual z-score checks