04-08: Confidence Interval Estimator¶

Home | All Projects | Notes | Exercises | Quiz Hub

Build a reusable interval estimator for a mean and for a proportion, work out the sample size needed for a target margin of error, and prove by simulation that a "95% interval" really does capture the truth 95% of the time.

Chapters applied: 09-01 · 09-02

Difficulty: ⭐⭐⭐ Intermediate–Advanced

The Data¶

File	Contents
`data/battery_life.csv`	45 battery lifetimes in hours — for the mean interval
`data/poll_responses.csv`	600 Yes/No poll responses — for the proportion interval

What You Produce¶

A CI calculator for a mean, in both the σ-known (z) and σ-unknown (t) versions
A CI calculator for a proportion, plus the Wilson and exact alternatives
A width comparison across 90%, 95%, and 99% confidence
Sample-size tables for a target margin of error
A CI for the variance using chi-square
A coverage simulation proving the 95% claim — and showing where the Wald interval fails

Excel Route¶

Step 1 — CI for a mean, from raw data¶

=COUNT(A2:A46)                                    ' n
=AVERAGE(A2:A46)                                  ' x-bar
=STDEV.S(A2:A46)                                  ' s
=STDEV.S(A2:A46)/SQRT(COUNT(A2:A46))              ' standard error
=COUNT(A2:A46)-1                                  ' df

=T.INV.2T(0.05, $E$5)                             ' critical t
=CONFIDENCE.T(0.05, $E$3, $E$1)                   ' margin of error E
=$E$2-$E$7 & " to " & $E$2+$E$7                   ' the interval

Faster route: Data ▸ Data Analysis ▸ Descriptive Statistics, tick Confidence Level for Mean: 95%. The last line of the output is E.

Step 2 — Three confidence levels¶

=CONFIDENCE.T(0.10, $E$3, $E$1)      ' 90%
=CONFIDENCE.T(0.05, $E$3, $E$1)      ' 95%
=CONFIDENCE.T(0.01, $E$3, $E$1)      ' 99%

Tabulate the lower bound, upper bound, and width. The width grows with confidence — that is the trade-off, and it should be visible in your own numbers.

Step 3 — CI for a proportion¶

=COUNTIF(B2:B601,"Yes")                                     ' x
=COUNTA(B2:B601)                                            ' n
=$H$1/$H$2                                                  ' p-hat
=SQRT($H$3*(1-$H$3)/$H$2)                                   ' standard error
=AND($H$2*$H$3>=5, $H$2*(1-$H$3)>=5)                        ' condition check -> TRUE
=NORM.S.INV(0.975)*$H$4                                     ' margin of error
=$H$3-$H$6 & " to " & $H$3+$H$6                             ' the interval

Warning

Proportion intervals use z, never t — there is no separate s to correct for. And they use p̂ in the standard error, unlike the hypothesis test in 10-02, which uses p₀.

Step 4 — Sample-size tables¶

' For a MEAN, with sigma estimated from the pilot data:
=ROUNDUP((NORM.S.INV(0.975)*$E$3/$K1)^2, 0)      ' K1 holds the target E

' For a PROPORTION:
=ROUNDUP(0.25*(NORM.S.INV(0.975)/$K1)^2, 0)      ' no prior estimate -> p = 0.5
=ROUNDUP($H$3*(1-$H$3)*(NORM.S.INV(0.975)/$K1)^2, 0)   ' using the pilot p-hat

Build a two-way Data Table (Data ▸ What-If Analysis ▸ Data Table) over target E down the side and confidence level across the top. The √n economics becomes obvious: halving E quadruples n.

Step 5 — CI for the variance¶

=(COUNT(A2:A46)-1)*VAR.S(A2:A46)/CHISQ.INV.RT(0.025, COUNT(A2:A46)-1)  ' lower for sigma^2
=(COUNT(A2:A46)-1)*VAR.S(A2:A46)/CHISQ.INV.RT(0.975, COUNT(A2:A46)-1)  ' upper for sigma^2
=SQRT(lower) & " to " & SQRT(upper)                                     ' for sigma

Note this interval is not symmetric about s² — the chi-square distribution is right-skewed.

Step 6 — Coverage simulation¶

' Pretend the true mean is 1218 and sigma is 96 (the values used to build the data).
' For each of 500 rows:
'   1. draw a sample of 45:  =NORM.INV(RAND(), 1218, 96)  across 45 columns
'   2. compute its mean and s
'   3. build the 95% interval
'   4. flag:  =IF(AND(lower<=1218, 1218<=upper), 1, 0)
=AVERAGE(flag_column)                 ' should be about 0.95

Press F9 repeatedly — the coverage hovers around 0.95. Individual intervals move; the truth never does.

R Route¶

Rscript r/analysis.R

Runs every calculator, the sample-size tables, and both coverage simulations, and writes ci_plots.png. See r/analysis.R.

Python Route¶

python python/analysis.py

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

Checkpoints¶

The mean interval uses t (because s was computed from the data)
The proportion interval uses z and checks np̂ ≥ 5, nq̂ ≥ 5
Higher confidence produces a wider interval, and this is shown numerically
Sample sizes are rounded up
The variance interval is noted as asymmetric about s²
The coverage simulation lands near 0.95, and the interpretation is stated correctly

Extend It¶

Compare the Wald, Wilson, plus-four, and Clopper-Pearson proportion intervals on the same data. How much do they differ at n = 600? At n = 20?
Run the coverage simulation at p = 0.05 with n = 20 and watch Wald coverage collapse to about 65%
Add a CI for the difference of two means and for the difference of two proportions
Plot 100 simulated intervals as horizontal lines with the true μ as a vertical rule — the classic picture of what "95% confidence" means