Excel Cheat Sheet — z-Tests (Type I α, Type II β, Power, p-value, sample size)

Context: student commute times. Use these copy-ready Excel formulas. No external add-ins needed.

Inputs (example values students can change)

Typical cells (or constants in LET)

  • Null mean μ0: 30 minutes
  • “True” mean for power μ1: e.g. 33.5 (pick a meaningful effect)
  • Std dev σ: 6.1 (known or good estimate)
  • Sample size n: 23
  • Significance α: 0.05
  • Observed sample mean ȳ (for p-value): e.g. 33.1

α chosen false-alarm rate. β miss rate at your chosen μ1. Power = 1−β.

Pick μ1 to reflect a practically important shift (e.g., +2.0 minutes), not to “game” power.

1) Standard Error (SE)

= sigma / SQRT(n)
= 6.1 / SQRT(23)
=LET(sigma,6.1, n,23, sigma/SQRT(n))

2) Critical value(s) and critical region on ȳ

Two-sided test (μ ≠ μ0)

z_star = NORM.S.INV(1 - alpha/2)
L      = mu0 - z_star * SE
U      = mu0 + z_star * SE

= NORM.S.INV(1-0.05/2)      // 1.959963985
= 30 - z* * (6.1/SQRT(23))  // L
= 30 + z* * (6.1/SQRT(23))  // U

Right-tailed (μ > μ0)

z_star = NORM.S.INV(1 - alpha)
U      = mu0 + z_star * SE     // reject if ȳ > U

Left-tailed (μ < μ0)

z_star = NORM.S.INV(1 - alpha)
L      = mu0 - z_star * SE     // reject if ȳ < L
LET block (auto-tail → returns SE, L, U) 
=LET(
  mu0,30, sigma,6.1, n,23, alpha,0.05, tail,"two",   /* "two","right","left" */
  SE, sigma/SQRT(n),
  z2, NORM.S.INV(1-alpha/2),
  z1, NORM.S.INV(1-alpha),
  L, IF(tail="two", mu0 - z2*SE, IF(tail="right", -1E99,        mu0 - z1*SE)),
  U, IF(tail="two", mu0 + z2*SE, IF(tail="right",  mu0 + z1*SE,  1E99     )),
  HSTACK(SE,L,U)
)

3) p-value for an observed mean ȳ

First compute the z statistic: z = (ybar - mu0) / SE

Two-sided

= 2 * (1 - NORM.S.DIST(ABS(z), TRUE))

Right-tailed

= 1 - NORM.S.DIST(z, TRUE)

Left-tailed

= NORM.S.DIST(z, TRUE)
One-shot LET for p-value (auto-tail) 
=LET(
  ybar,33.1, mu0,30, sigma,6.1, n,23, tail,"two",
  SE, sigma/SQRT(n),
  z, (ybar-mu0)/SE,
  IF(tail="two", 2*(1-NORM.S.DIST(ABS(z),TRUE)),
     IF(tail="right", 1-NORM.S.DIST(z,TRUE), NORM.S.DIST(z,TRUE)))
)

4) Type II error (β) and Power at a chosen μ1

Choose a meaningful μ1 (e.g., 32.0 or 33.5). Power = 1−β.

Two-sided

β     = NORM.DIST(U, mu1, SE, TRUE) - NORM.DIST(L, mu1, SE, TRUE)
Power = 1 - β

Right-tailed

β     = NORM.DIST(U, mu1, SE, TRUE)
Power = 1 - β

Left-tailed

β     = 1 - NORM.DIST(L, mu1, SE, TRUE)
Power = 1 - β
LET block (auto-tail → returns Power) 
=LET(
  mu0,30, mu1,33.5, sigma,6.1, n,23, alpha,0.05, tail,"two",
  SE, sigma/SQRT(n),
  z2, NORM.S.INV(1-alpha/2),
  z1, NORM.S.INV(1-alpha),
  L, IF(tail="two", mu0 - z2*SE, IF(tail="right", -1E99,        mu0 - z1*SE)),
  U, IF(tail="two", mu0 + z2*SE, IF(tail="right",  mu0 + z1*SE,  1E99     )),
  beta,
    IF(tail="two",
       NORM.DIST(U, mu1, SE, TRUE) - NORM.DIST(L, mu1, SE, TRUE),
     IF(tail="right",
       NORM.DIST(U, mu1, SE, TRUE),
       1 - NORM.DIST(L, mu1, SE, TRUE))),
  1 - beta
)

5) Sample size for target Power (planning)

Let δ = |μ1 − μ0| be the smallest meaningful difference you want to detect.

Two-sided (approx., normal)

=ROUNDUP( ((NORM.S.INV(1-alpha/2) + NORM.S.INV(power)) * sigma / delta)^2 , 0 )

One-sided

=ROUNDUP( ((NORM.S.INV(1-alpha) + NORM.S.INV(power)) * sigma / delta)^2 , 0 )
Example (α=0.05, power=0.80, σ=6.1, δ=2.0) 
=ROUNDUP( ((NORM.S.INV(0.975) + NORM.S.INV(0.80)) * 6.1 / 2)^2 , 0 )

6) Confidence interval (z-CI) link

(1−α) CI for the mean using ȳ and SE:

Lower = ybar - NORM.S.INV(1-alpha/2) * SE
Upper = ybar + NORM.S.INV(1-alpha/2) * SE

Two-sided equivalence: Reject H0 iff μ0 is outside the (1−α) CI.

7) Quick reporting line (fill placeholders)

SE = [σ/√n]. Two-sided z-test at α = [α]. Critical region on ȳ: [L, U].
Observed z = (ȳ − μ0)/SE; p-value = [p].
At μ1 = [chosen effect], β = [β], Power = [1−β].
Decision: [Reject / Fail to reject] H0.
Interpretation: [plain-English sentence for the commute context].

8) Reminders students always forget