Excel like a finance pro.
← The libraryPractice · 2 questions →
Statistical function
Z.TEST
Returns the one-tailed p-value of a z-test.
Rarely usedDifficulty 1550 · AdvancedUsage rank #460 of 520
When to use it
The one-tailed p-value of a z-test: the probability that the sample mean would be at least this high if the true mean were x. Sigma is optional; the sample sd is used if omitted.
The shape of it
- Syntax
=Z.TEST(array, x, [sigma])
Worked examples
Against 4
=Z.TEST({3,6,7,8,6,5,4,2,1,9},4) → 0.090574
Sample mean 5.1 against a hypothesized 4.
Against 6
=Z.TEST({3,6,7,8,6,5,4,2,1,9},6) → 0.863043
Against 6, which is above the sample mean.
Two tails
=2*MIN(Z.TEST(A2:A11,4),1-Z.TEST(A2:A11,4)) → 0.181148
Two-tailed version.
Worth knowing
- One-tailed by design; double the smaller tail for two-tailed.
- Use T.TEST for small samples with unknown sd.
- Pass sigma when the population sd is known.
Where it goes wrong
- #N/A when the array is empty.
- #NUM! when the sample has zero variance and no sigma is given.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.