Quick Navigation

Z.TEST Function

Excel 2007+

Summary

The Z.TEST function performs a one-tailed z-test, returning the probability that the sample mean from your data exceeds a specified hypothesized population mean. This statistical tool helps determine if your sample data significantly differs from an expected population value.

Syntax

Z.TEST(array, x, [sigma])

Parameters

Parameter	Type	Required	Description
array	`Array/Range`	Yes	The array or range of sample data to analyze
x	`Number`	Yes	Hypothesized population mean value for comparison
sigma	`Number`	No	Known population standard deviation (sample std dev used if omitted)

Using the Z.TEST Function

Z.TEST is a powerful statistical function for hypothesis testing in Excel. Use it to test whether your sample mean significantly differs from a hypothesized population mean. Perfect for quality control, A/B testing, and research analysis where you need to validate assumptions about population parameters.

Common Z.TEST Examples

One-Tailed Z-Test (Hypothesized Mean = 4)

=Z.TEST(A2:A11,4)

Tests if sample mean > 4 using sample std dev. Returns 0.090574 indicating 9.06% probability.

Two-Tailed Z-Test Formula

=2*MIN(Z.TEST(A2:A11,4),1-Z.TEST(A2:A11,4))

Calculates two-tailed p-value (0.181148) by taking minimum of one-tailed result and its complement, then doubling.

Z-Test with Known Population Std Dev

=Z.TEST(A2:A11,4,2.5)

Uses specified population std dev of 2.5 instead of calculating from sample.

Frequently Asked Questions

#N/A error value is returned when no data is provided.

Use formula: 2 * MIN(Z.TEST(array,x), 1-Z.TEST(array,x))

Specify sigma when you know the true population standard deviation; otherwise omit for sample std dev.

Common Errors and Solutions

#N/A Error

Cause: Array contains no numeric data or is empty

Solution: Ensure array has valid numeric observations

#VALUE! Error

Cause: Non-numeric values in array or invalid x/sigma

Solution: Clean data and ensure numeric inputs

Unexpected results >0.5

Cause: Sample mean < hypothesized mean (normal due to one-tailed nature)

Solution: Consider two-tailed formula for non-directional tests

Notes

Returns one-tailed p-value (right tail)
If sample mean < x, result > 0.5 due to normal distribution symmetry
Formula: 1-NORM.S.DIST((AVERAGE(array)-x)/(sigma/√n),TRUE)
Excel 2007+ only - use T.TEST for older versions

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in: Excel 2003 and earlier

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+