Skip to content

04-06: Normal Distribution Explorer

Statistics & Probability

View the live site — ijk37.com

Project 06

Home  |  All Projects  |  Notes  |  Exercises  |  Quiz Hub

Work the normal distribution in both directions — value → probability and probability → value — then use it on real measurement data and check whether the normality assumption actually holds.

Chapters applied: 04-02 · 07-02

Difficulty: ⭐⭐ Intermediate

The Data

data/heights_cm.csv — 200 adult heights in centimetres, drawn from a genuinely normal process so the diagnostics have something clean to confirm.

What You Produce

  1. A two-way calculator: any normal probability, and any normal percentile
  2. A z-score table for the data, with the unusual values flagged
  3. An empirical-rule check: what percentage of the data actually falls within ±1σ, ±2σ, ±3σ?
  4. A normality assessment: histogram with the fitted curve, boxplot, Q-Q plot, skewness, kurtosis, Shapiro-Wilk
  5. A normal approximation to the binomial, with and without the continuity correction

Excel Route

Step 1 — The calculator block

Input cells: B1 = μ, B2 = σ, B3 = x, B4 = probability, B5 = a, B6 = b.

' VALUE  ->  PROBABILITY
=NORM.DIST(B3, B1, B2, TRUE)                      ' P(X < x)     left tail
=1-NORM.DIST(B3, B1, B2, TRUE)                    ' P(X > x)     right tail
=NORM.DIST(B6,B1,B2,TRUE)-NORM.DIST(B5,B1,B2,TRUE)' P(a < X < b) between
=NORM.DIST(B3, B1, B2, FALSE)                     ' the DENSITY (rarely needed)

' Through the z-score, to show the method
=STANDARDIZE(B3, B1, B2)                          ' z
=NORM.S.DIST(D5, TRUE)                            ' P(Z < z)

' PROBABILITY  ->  VALUE   (the INVERSE direction)
=NORM.INV(B4, B1, B2)                             ' the value with B4 area to its left
=NORM.INV(1-B4, B1, B2)                           ' the value with B4 area to its right
=NORM.INV(0.025, B1, B2) & " to " & NORM.INV(0.975, B1, B2)   ' middle 95%
=NORM.S.INV(B4)                                   ' the critical z

Tip

Which function? If the problem GIVES a value and asks for an area, use NORM.DIST. If it GIVES an area and asks for a value, use NORM.INV. That one sentence resolves most normal-distribution confusion.

Step 2 — Z-scores for the data

=AVERAGE(A2:A201)                                          ' x-bar
=STDEV.S(A2:A201)                                          ' s
=STANDARDIZE(A2, $D$1, $D$2)                               ' B2: z, fill down
=IF(ABS(B2)>3,"VERY UNUSUAL", IF(ABS(B2)>2,"unusual",""))  ' C2: flag
=COUNTIF(C2:C201,"unusual") & " / " & COUNTIF(C2:C201,"VERY UNUSUAL")

Sanity check: AVERAGE(B2:B201) must be 0 and STDEV.S(B2:B201) must be 1.

Step 3 — Empirical-rule check

=COUNTIFS(A2:A201,">="&$D$1-$D$2, A2:A201,"<="&$D$1+$D$2)/200      ' within 1 SD -> ~0.68
=COUNTIFS(A2:A201,">="&$D$1-2*$D$2, A2:A201,"<="&$D$1+2*$D$2)/200  ' within 2 SD -> ~0.95
=COUNTIFS(A2:A201,">="&$D$1-3*$D$2, A2:A201,"<="&$D$1+3*$D$2)/200  ' within 3 SD -> ~0.997

Put the observed and theoretical figures side by side. If they diverge badly, the data is not bell-shaped and the empirical rule does not apply — use Chebyshev instead.

Step 4 — Normality assessment

=SKEW(A2:A201)                          ' near 0 if normal
=KURT(A2:A201)                          ' Excel reports EXCESS kurtosis; near 0 if normal
=(AVERAGE(A2:A201)-MEDIAN(A2:A201))     ' near 0 if symmetric

Q-Q plot by hand — the most informative check, and Excel has no built-in:

' 1. Sort the data ascending into column F
' 2. G2:  =NORM.S.INV((ROW()-1.5)/200)      the expected z-score for that rank
' 3. Select F:G  ▸  Insert ▸ Scatter (Markers only)
' 4. Add a trendline. Points close to a straight line  =>  normal.

Also add: Insert ▸ Insert Statistic Chart ▸ Histogram, then overlay the fitted normal curve as a second series computed with =NORM.DIST(x, μ, σ, FALSE)*n*bin_width.

Step 5 — Normal approximation to the binomial

' n = 200, p = 0.55, find P(at least 120)
=200*0.55                                          ' np  = 110  >= 5  OK
=200*0.45                                          ' nq  =  90  >= 5  OK
=SQRT(200*0.55*0.45)                               ' sigma = 7.0356
=1-NORM.DIST(119.5, 110, 7.0356, TRUE)             ' WITH continuity correction -> 0.08849
=1-NORM.DIST(120,   110, 7.0356, TRUE)             ' WITHOUT it                -> 0.07756
=1-BINOM.DIST(119, 200, 0.55, TRUE)                ' EXACT binomial            -> 0.08847

The corrected approximation is within 0.00002 of exact; the uncorrected one is off by 0.011 — more than 500× worse.

R Route

Rscript r/analysis.R

Runs the calculator, the diagnostics, and the approximation, and writes normal_plots.png. See r/analysis.R.

Python Route

python python/analysis.py

Same via scipy.stats. See python/analysis.py.

Checkpoints

  • NORM.DIST and NORM.INV are used in the right directions
  • Standardized values have mean 0 and SD 1
  • The observed ±1σ/±2σ/±3σ percentages are compared with 68/95/99.7
  • A Q-Q plot is produced and interpreted, not just a histogram
  • np ≥ 5 and nq ≥ 5 are checked before the normal approximation
  • The continuity correction is applied, and its effect is quantified

Extend It

  • Add a Chebyshev column and show it is always satisfied but always weaker than the empirical rule
  • Compute the percentile rank of a given height and the height at a given percentile, and check they invert each other
  • Repeat every diagnostic on a deliberately skewed column (e.g. rexp) and see each one fail
  • Add the inverse-transform trick: NORM.INV(RAND(), μ, σ) generates normal random values from uniform ones