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.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✖ |
| Wps | ✔ |
| Zoho | ✔ |
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
Z.TEST always returns a one-tailed probability.
For two-tailed tests, multiply the result by 2.
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
Z.TEST is your go-to tool for fast, reliable hypothesis testing — perfect for quality control, scientific analysis, and any situation where you need to know whether a sample mean is significantly different from a target value.