ZTEST in Apple Numbers: One-Tailed Probability and Sigma
Calculate a one-tailed z-test p-value from a Numbers sample with a supplied population standard deviation or its documented default.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Apple Numbers. How formula examples are checked
Quick answer. With values 57, 75, 66, 98, 92, 80 in cells B2:G2, =ZTEST(B2:G2,70,9) has Apple’s published expected result 0.0147281928162857. It compares the sample mean against a hypothesized value of 70, using known population standard deviation 9, and returns a one-tailed probability. It is not a two-sided test unless you explicitly build and interpret that calculation. [A1, C1]
Purpose and interpretation
ZTEST evaluates a mean relative to a stated comparison value using a normal-model standardized statistic. With n observations, sample mean x̄, null mean μ₀ and provided population sigma σ, the independent mathematical cross-check is z=(x̄−μ₀)/(σ/√n); the upper-tail probability is 1−Φ(z). Apple describes the output as one-tailed and its sample example agrees with the upper-tail calculation. The mathematical interpretation assumes appropriate independent measurements and a justified sampling distribution—conditions the function cannot inspect. [A1, C1]
A p-value is not the probability that a null hypothesis is true. Unlike Apple’s two-sample TTEST function, ZTEST is a one-sample calculation; do not insert two unrelated group ranges as its first and second arguments. [A1, C1]
Exact syntax
=ZTEST(value-set, value, stdev)
| Position | Apple argument | Apple type | Required? | Meaning | Documented default |
|---|---|---|---|---|---|
| 1 | value-set |
Collection of values of one common type: number, date/time or duration | Yes | Observed sample | None |
| 2 | value |
A single number, date/time or duration | Yes | Hypothesized comparison | None |
| 3 | stdev |
Number greater than zero | No | Population standard deviation | If omitted, the sample standard deviation is used |
The third argument is optional; the first two are not. Apple explicitly permits numeric, date/time and duration collections if values all share the same type. Those supported types must not be mistaken for a blanket statement that every mixed-type subtraction works or that every other spreadsheet’s coercion rules apply. The optional-standard-deviation behaviour is Apple’s documented rule, not an invented implicit population-sigma value. [A1, A4]
Reproducible Numbers worksheet
Make a table named Scores, with six measurement cells B2:G2:
| Cell | B2 | C2 | D2 | E2 | F2 | G2 |
|---|---|---|---|---|---|---|
| Value | 57 | 75 | 66 | 98 | 92 | 80 |
Put the comparison value 70 in J2 and known population sigma 9 in J3. In an empty cell, enter:
=ZTEST(B2:G2,$J$2,$J$3)
Expected probability 0.0147281928162857. The sample mean is 78 and the known-sigma z-statistic is ≈2.1773242158. The result is small because the sample mean is above the hypothesized mean in this example. Apple itself publishes the equivalent literal formula =ZTEST(B2:G2,70,9); the cell-reference rewrite is a documented table-reference construction but has not been executed natively. [A1, A3, C1]
Now intentionally omit the standard deviation:
=ZTEST(B2:G2,70)
Apple documents that the sample standard deviation is used when the third argument is absent. For these six numbers the independently calculated sample standard deviation is ≈15.4531550177; applying the documented mathematical model gives an expected one-tail probability of approximately 0.1023837542. This derived output is not printed in Apple’s example and has not been checked in Numbers. The change occurs because estimated sigma is much larger than the supplied 9; you should not switch between them after looking at p-values. [A1, C1]
Advanced decisions and direction
If a statistical plan genuinely calls for a two-sided comparison, a mathematical conversion of an upper-tail p to a two-sided normal-model probability is 2×MIN(p,1−p). With the sample and known sigma above:
=2*MIN(ZTEST(B2:G2,70,9),1-ZTEST(B2:G2,70,9))
Independently expected: approximately 0.0294563856325714. This is a derived normal-theory calculation, not an additional ZTEST mode and not native-tested. Decide whether the hypothesis is one- or two-sided before examining data. The use of MIN and arithmetic is part of generic Numbers formula composition; Apple documents ZTEST’s single-tail output but does not document this composite formula as an official example. [A1, A2, C1]
A one-tail result near 0.015 can support evidence in an upper-direction question under its assumptions; the same number is not automatically the correct directional p-value for a lower-direction hypothesis. Distinguish research question, sign of the observed difference and test statistic. [C1]
To avoid hard-coded parameters in a repeated audit, place the hypothesized mean in J2 and the population standard deviation in J3, use $J$2/$J$3 and fill formulas down new numeric observation blocks only if each block has a consistent sample size and type. Otherwise the fill-down process can silently compare different groups. [A3]
Common errors, boundaries and data-quality checks
| Problem | Check / correction |
|---|---|
| ZTEST syntax isn’t accepted | Use two required arguments, optional third argument, and the separator appropriate to the decimal locale. |
stdev is zero or negative |
Apple specifies stdev > 0; enter a genuinely positive population sigma or omit the argument if justified. Do not invent a numerical default. |
| Blank, text or mixed date/duration data | Keep the sampled values of one Apple-supported type, check the actual cell formatting and avoid relying on another product’s coercion rules. |
| Known sigma versus estimated sigma produce different p-values | This is expected; the third argument changes the denominator. Apple says sample standard deviation is substituted when omitted. |
| Only one observation, or all equal values with omitted sigma | Sample standard deviation or standard error may not be meaningful; Apple’s page does not specify exact error text for these boundaries. |
| Significance interpretation reversed | ZTEST returns one tail; whether that tail corresponds to the prespecified claim matters. A p-value does not prove a model or hypothesis. |
| Results formatted as percentages | A decimal 0.014728 corresponds to about 1.4728%, not 0.014728%. Numeric display formatting does not change its underlying probability. |
Apple’s documentation permits date/time and duration types but the reproducible examples here intentionally use numbers. There were no native type-coercion, time-zone, error-code or portability tests. Avoid declaring historical platform minima without Apple release evidence. [A1, A4]
Related official help (reviewed links)
- Apple TTEST — two-sample and paired t-test probabilities, a different design and degrees-of-freedom model.
- Apple PROB — probabilities for a known finite discrete distribution, not a test of a sample mean.
- Apple: Types of arguments and values for the meaning of numeric/duration/collection input types.
Numbers version, table references, and locale
These instructions follow Apple’s English Formulas and Functions Help, version 15.4, reviewed 13 October 2026. Apple describes the Functions Browser for Numbers on Mac and on iPhone/iPad, and in relevant iWork apps on iCloud.com, but the documentation version is not proof that every function exists in every historical app release or platform build. There was no native Numbers execution for this guide. Do not represent the expected outputs as measured application results. [A1, A4]
The examples use English function names, a period as decimal point and a comma between arguments. Apple explicitly says that a Numbers setup using a comma for decimals requires a semicolon between function arguments when typing formulas. Spreadsheet language/region settings also affect numeric punctuation; select the appropriate localized function spelling in your own Functions Browser as translated names may differ. [A2, A5]
Inside a Numbers table, B2:C6 identifies the rectangular range and $D$2 is an absolute reference. A cross-table reference can be written as Table Name::B2 or Sheet Name::Table Name::B2 where required, and Numbers can insert the appropriate reference when you select a source cell. The formulas below assume all inputs and the result are in the same named table; do not copy an ambiguous short reference across multiple tables. [A3]
Scope of verification
- Official documentation check: Apple’s named function help, argument/type and cell-reference instructions, and the functions-overview regional-syntax rule were read on 13 October 2026. The source links and claim map are preserved below.
- Independent calculations: Expected numbers were recomputed with Python/SciPy or exact arithmetic; these checks establish the mathematics and consistency with Apple’s published examples, not the installed application’s behaviour.
- Not performed: native Apple Numbers execution, screenshots, localized behavior or historical-platform regression tests.
Official references checked (13 October 2026)
- [A1] Function specification: ZTEST — Apple Formulas and Functions Help 15.4. Defines syntax and supported argument descriptions; the examples explicitly attributed to Apple come from this page.
- [A2] Formula syntax/locale: Apple: Functions overview. Confirms comma separators in the displayed English syntax and semicolons if the system uses a comma decimal separator.
- [A3] Cell and table references: Apple: Refer to cells in formulas. Documents range notation, absolute references and
Table::Cell. - [A4] Collections/value types and platforms: Apple: Types of arguments and values; Apple: Formulas and Functions Help welcome. Specifies collection, number and modal types and the general device context.
- [A5] Document language and region: Apple: Format a spreadsheet for another language in Numbers on Mac. Describes regional formatting changes, not an exhaustive translation/alias map.
- [C1] Independent mathematical calculations: Python/SciPy calculations reproduced the stated examples; these are not native Numbers tests.