📘 Excel Quick Reference — Chapter 8

Click Copy to grab any formula. Layouts are consistent so students don’t get lost.

z-Interval (σ known) t-Interval (σ unknown) χ²-Interval (variance / SD) Prediction Interval Tolerance Interval

1) z-Interval for μ (σ known)

Inputs (cells)

CellMeaning
B2x̄ (sample mean)
B3σ (population SD, known)
B4n (sample size)
B5α (two-sided, e.g., 0.05 for 95%)

Alt: If you prefer confidence in B5 (e.g., 0.95), set =1-$B$5 as α in another cell.

Formulas (cells)

CellFormula
B6z-critical → =NORM.S.INV(1 - $B$5/2)
B7SE → =$B$3/SQRT($B$4)
B8ME → =$B$6*$B$7
B9Lower → =$B$2-$B$8
B10Upper → =$B$2+$B$8

All-in-one ME: =NORM.S.INV(1-$B$5/2)*$B$3/SQRT($B$4)

Commute example: x̄=31.4, σ=8, n=36, α=0.05 ⇒ z=1.960; SE=1.333; ME≈2.613; CI≈[28.787, 34.013].
Plan n for target ME* (put target ME in B11): =ROUNDUP((NORM.S.INV(1-$B$5/2)*$B$3/$B$11)^2,0)

2) t-Interval for μ (σ unknown)

Inputs (cells)

CellMeaning
B2x̄ (sample mean)
B3s (sample SD)
B4n (sample size)
B5α (two-sided, e.g., 0.05)
df = n−1. Use two-tailed t.

Formulas (cells)

CellFormula
B6df → =$B$4-1
B7t-critical → =T.INV(1-$B$5/2,$B$6) or =T.INV.2T($B$5,$B$6)
B8SE → =$B$3/SQRT($B$4)
B9ME → =$B$7*$B$8
B10Lower → =$B$2-$B$9
B11Upper → =$B$2+$B$9

All-in-one ME: =T.INV(1-$B$5/2,$B$4-1)*$B$3/SQRT($B$4)

Commute example: x̄=31.4, s=8, n=10, α=0.05 ⇒ df=9; t≈2.262; SE≈2.530; ME≈5.724; CI≈[25.677, 37.123].
Plan n (refine): start with z to get a first n (above), then recompute ME using t with that n. Iterate if needed.

3) χ²-Interval for Variance and SD

Inputs (cells)

CellMeaning
B2s (sample SD)
B3n (sample size)
B4α (two-sided, e.g., 0.05)
Assumes approximate Normality of the data; df = n−1.

Formulas (cells)

CellFormula
B5df → =$B$3-1
B6χ²α/2 (left-tail CDF) → =CHISQ.INV($B$4/2,$B$5)
B7χ²1−α/2 (left-tail CDF) → =CHISQ.INV(1-$B$4/2,$B$5)
B8σ² lower → =$B$5*$B$2^2/$B$7
B9σ² upper → =$B$5*$B$2^2/$B$6
B10σ lower → =SQRT($B$8)
B11σ upper → =SQRT($B$9)

Right-tail alternative (equivalent): =CHISQ.INV.RT(1-$B$4/2,$B$5) and =CHISQ.INV.RT($B$4/2,$B$5).

Commute spread (s=8, n=10, α=0.05): df=9; χ²0.025≈2.700; χ²0.975≈19.02. Var CI≈[30.3, 213.3]; SD CI≈[5.503, 14.605] minutes.
Decision vs σ≤6: If 6 lies inside [σ lower, σ upper], result is inconclusive. If σ upper ≤ 6 → supports ≤6. If σ lower > 6 → likely >6.

4) Prediction Interval (one future observation)

Inputs (cells)

CellMeaning
B2x̄ (sample mean)
B3s (sample SD)
B4n (sample size)
B5α (two-sided)

Formulas (cells)

CellFormula
B6df → =$B$4-1
B7t-critical → =T.INV(1-$B$5/2,$B$6)
B8PI ME → =$B$7*$B$3*SQRT(1+1/$B$4)
B9Lower → =$B$2-$B$8
B10Upper → =$B$2+$B$8

PI > CI (wider) because it predicts an individual future value.

5) Tolerance Interval (two-sided, coverage γ with confidence 1−α)

Inputs (cells)

CellMeaning
B2x̄
B3s
B4n
B5α (confidence, e.g., 0.05)
B6γ (coverage, e.g., 0.95)

Exact k depends on n, α, γ (Howe/Young methods). Below is a normal-based quick approximation.

Approx k (quick, conservative)

CellFormula
B7 zγ → =NORM.S.INV((1+$B$6)/2)
B8 tα → =T.INV(1-$B$5,$B$4-1)
B9 k (approx) → =($B$7 + $B$8*SQRT(1+1/$B$4))
B10Lower → =$B$2-$B$9*$B$3
B11Upper → =$B$2+$B$9*$B$3

Grading focus: understanding **coverage vs confidence** and when to use TI vs CI/PI.

Reminders