Session 4.5 — Normal Distribution - ICE

Use this as a step‑by‑step walkthrough in class. The examples below use hypothetical Statics grade dataset (n = 150) with sample statistics \(\mu \approx 83.54\) and \(\sigma \approx 7.44\) (sample stdev).

1) Introduction: Why Normal Distribution?

  • This dataset represents 150 students’ Statics grades.
  • Many real‑world outcomes (exam scores, heights, measurement errors) cluster around an average and form a bell‑shaped curve.
  • Goal: summarize the distribution with mean \(\mu\) and standard deviation \(\sigma\), then use them to answer probability questions.

2) Step 1: Compute Descriptive Statistics in Excel

Assume grades are in B2:B151.

MeasureExcel FormulaMeaning
Mean (\(\mu\))=AVERAGE(B2:B151)Center of the distribution
Standard Deviation (\(\sigma\))=STDEV.S(B2:B151)Spread of the grades (use STDEV.S for a sample)
Min / Max=MIN(B2:B151) / =MAX(B2:B151)Range check
Count=COUNT(B2:B151)Sample size

For our dataset: \(n=150\), \(\mu\approx83.54\), \(\sigma\approx7.44\), min = 69, max = 100.

3) Step 2: Create a Histogram

  1. Select the grade column → Insert → Statistical → Histogram.
  2. Edit binning: right‑click the horizontal axis → Format Axis → set Bin width = 5 (≈ 65–70, 70–75, …, 95–100).
  3. Observe how the shape approaches a bell curve centered near \(\mu\).
  4. (Optional) Overlay a normal curve:
    • Compute expected frequency per bin using NORM.DIST() at bin centers and multiply by bin width and \(n\).
    • Or add a smooth line series using those expected counts.

4) Step 3: z‑Scores and Probabilities

Standardize any grade \(x\) to a z‑score:

\[ z = \frac{x - \mu}{\sigma} \]

Excel (if mean in D2 and stdev in D3): =(B2-$D$2)/$D$3

Find Probabilities

TaskExcel FormulaMath Equivalent
Left‑tail \(P(X\le x)\)=NORM.DIST(x, μ, σ, TRUE)\(F(x)\)
Right‑tail \(P(X\ge x)\)=1-NORM.DIST(x, μ, σ, TRUE)\(1-F(x)\)
Between \(P(a\le X\le b)\)=NORM.DIST(b, μ, σ, TRUE)-NORM.DIST(a, μ, σ, TRUE)\(F(b)-F(a)\)
Percentile → x=NORM.INV(p, μ, σ)solve \(x=\mu+\sigma z_p\)

What is a z‑Score? (And Why It’s Important)

The z‑score transforms any normal random variable \(X\sim\mathcal{N}(μ,σ^2)\) into the standard normal \(Z\sim\mathcal{N}(0,1)\):

\[ z = \frac{x - μ}{σ} \]

This rescaling centers the distribution at 0 and makes the spread 1. Historically, before computers, students used printed z‑tables to look up \(Φ(z)=P(Z≤z)\) and find probabilities. Today, Excel and calculators compute \(P(X≤x)\) directly, but z‑scores are still crucial for:

  • Universality: One set of tables (or one function) works for any normal distribution once you convert to z.
  • Interpretability: z tells you how many standard deviations above/below the mean you are — an intuitive measure of extremeness.
  • Mathematical proofs: In statistics, derivations (CLT, sampling distributions, hypothesis tests) always standardize to Z for simplicity.
  • Inference foundations: Confidence intervals and z‑tests rely on comparing a computed z to critical values (e.g. ±1.96 for 95%).

Bottom line: Excel doesn’t require you to compute z to get probabilities, but learning z builds conceptual understanding and prepares students for hypothesis testing and theoretical statistics.

5) Step 4: ICE Examples (using our dataset)

  1. What % scored below 80?
    Excel: =NORM.DIST(80, μ, σ, TRUE)
    With \(\mu\approx83.54\), \(\sigma\approx7.44\): \(\approx 0.317\) → 31.7%
  2. What grade is the top 10% cutoff?
    Excel: =NORM.INV(0.90, μ, σ)
    Result with our data: \(\approx 93.07\)
  3. What % scored between 70 and 90?
    Excel: =NORM.DIST(90, μ, σ, TRUE)-NORM.DIST(70, μ, σ, TRUE)
    Result with our data: \(\approx 0.773\) → 77.3%

6) Step 5: Concept Reinforcement

  • 68‑95‑99.7 Rule: \(\mu\pm\sigma\approx[76.10, 90.98]\), \(\mu\pm2\sigma\approx[68.67, 98.41]\), \(\mu\pm3\sigma\approx[61.23, 105.85]\) (cap at 100 in practice).
  • Outliers: look for \(|z|>2\) (unusual) or \(|z|>3\) (rare).
  • Model check: compare the histogram bars to the smooth normal curve. If tails are heavier/lighter or skewed, normal may be an approximation.

Appendix — Copy‑Block for Excel

Mean: =AVERAGE(B2:B151)
Stdev (sample): =STDEV.S(B2:B151)
Left tail P(X≤x): =NORM.DIST(x, $D$2, $D$3, TRUE)
Right tail P(X≥x): =1-NORM.DIST(x, $D$2, $D$3, TRUE)
Between a..b: =NORM.DIST(b,$D$2,$D$3,TRUE)-NORM.DIST(a,$D$2,$D$3,TRUE)
Percentile → x: =NORM.INV(p, $D$2, $D$3)
z-score for cell B2: =(B2-$D$2)/$D$3

Replace x, a, b, and p with your chosen values. Cells D2 and D3 hold the computed mean and stdev.

Dataset — Statics Grades (n=150)

Get the full dataset and open it in Excel for practice.
student idStatics_Grade
1 75
2 71
3 74
4 90
5 74
6 100
7 86
8 83
9 75
10 88
11 77
12 83
13 100
14 81
15 91
16 76
17 82
18 96
19 77
20 96
21 88
22 78
23 87
24 90
25 87
26 69
27 76
28 80
29 81
30 87
31 83
32 71
33 85
34 87
35 86
36 91
37 89
38 86
39 81
40 69
41 85
42 84
43 84
44 72
45 73
46 90
47 82
48 87
49 82
50 82
51 90
52 78
53 83
54 78
55 79
56 80
57 84
58 78
59 92
60 75
61 81
62 78
63 94
64 84
65 90
66 70
67 84
68 89
69 83
70 91
71 78
72 93
73 100
74 79
75 78
76 94
77 95
78 78
79 79
80 80
81 71
82 75
83 74
84 76
85 82
86 84
87 94
88 74
89 90
90 80
91 82
92 87
93 73
94 85
95 83
96 86
97 84
98 100
99 77
100 78
101 77
102 78
103 77
104 92
105 93
106 77
107 75
108 86
109 78
110 87
111 84
112 70
113 94
114 96
115 77
116 79
117 84
118 85
119 87
120 98
121 81
122 76
123 71
124 76
125 82
126 96
127 78
128 84
129 82
130 92
131 100
132 78
133 78
134 90
135 87
136 97
137 87
138 79
139 87
140 91
141 89
142 86
143 91
144 91
145 93
146 87
147 81
148 83
149 92
150 75

7) Mini Calculator — z, Probabilities, and Inverse (works with any dataset)

Enter your dataset’s mean (μ) and stdev (σ), or click “Use practice dataset”. Once μ and σ are known, the entire distribution is defined — no need to reference every data point!

Mean (μ)
Stdev (σ)

A) z & Left-Tail Probability

x
z = (x−μ)/σ
P(X ≤ x)

B) Inverse: Given p, find z and x

p (0–1)
zp = Φ⁻¹(p)
x = μ + σ·zp

C) Middle Probability

a
b
P(a ≤ X ≤ b)
Concept Note: Once we have μ and σ, we no longer need the original data to answer probability questions.
  • The normal curve is completely determined by μ and σ.
  • Any x can be turned into a z-score and looked up on the standard normal curve.
  • This is why Excel’s NORM.DIST() works directly with x, μ, and σ.

Excel equivalents: NORM.DIST(x, μ, σ, TRUE), 1−NORM.DIST(x, μ, σ, TRUE), NORM.DIST(b, μ, σ, TRUE)−NORM.DIST(a, μ, σ, TRUE), NORM.INV(p, μ, σ).