Confidence intervals

01The problem

Ashgrove Chambers is negotiating an overdraft with its bank, sized to cover the gap between issuing invoices and getting paid. The finance partner asks:

"How long do clients take to pay? The bank wants a number we can stand behind."

The average across the 327 paid invoices so far is 45.4 days. But those invoices are a sample of the firm's billing, and next year's clients won't behave identically. "45.4 days" sounds more precise than it is. A confidence interval says how precise it really is: "between about 43 and 48 days, with 95% confidence". That's a number the firm can stand behind, and it tells the bank how much buffer to allow.

02The concept

What a confidence interval is

A 95% confidence interval is a range built from a sample so that, if you repeated the sampling many times, 95% of the ranges built this way would contain the true value. In practice: it's the range of values the true number plausibly lies in, given the sample.

It's built from the standard error (lesson 7):

estimate ± margin of error, where for a mean the margin ≈ 1.96 × SE

The 1.96 comes from the normal distribution: 95% of values lie within 1.96 SDs of the mean.

For a mean, in Excel

=CONFIDENCE.T(0.05, STDEV.S(range), COUNT(range))

returns the margin of error for a 95% interval (0.05 = 5% left over). The interval is AVERAGE(range) ± margin. CONFIDENCE.T uses the t-distribution, which makes the interval slightly wider for small samples. With more than about 30 values it's almost the same as 1.96 × SE.

For a percentage (a proportion)

For a share p (as a decimal) from n observations:

margin = 1.96 × √( p × (1 − p) ÷ n )

In Excel: =1.96 * SQRT(p * (1 - p) / n). This works well when there are at least 10 "yes" and 10 "no" answers; for rarer events, use a bigger sample.

Reading intervals correctly

• Wider interval = less certain. Smaller samples and noisier data give wider intervals.
• Overlapping intervals for two groups mean the difference might be chance; the formal check is a test (next lesson). Intervals that don't overlap at all mean the difference is very unlikely to be chance.
• An interval only covers sampling error. It says nothing about a biased sample, bad data, or a change in the future.
• Don't say "there's a 95% chance the true value is in this interval". Say "we're 95% confident the true value is between X and Y". (The true value is fixed; it's the interval that varies.)

Choosing the confidence level

95% is the convention. 90% gives a narrower interval with less confidence; 99% a wider one with more. Use CONFIDENCE.T(0.10, …) for 90% or CONFIDENCE.T(0.01, …) for 99%. Pick one before you look at the results, and say which you used.

03Example

Ashgrove's days to pay, for the 327 paid invoices:

=AVERAGE(days)                              45.4
=STDEV.S(days)                              25.6
=CONFIDENCE.T(0.05, STDEV.S(days), COUNT(days))   2.8

The 95% confidence interval is 45.4 ± 2.8: about 42.6 to 48.2 days.

For the bank: "Clients pay in 45 days on average; we're 95% confident the true average is between 43 and 48 days. Individual invoices vary much more (some take 90 days), so the overdraft should cover the slow payers, not just the average." The last sentence matters: the interval is about the average, not about any one invoice.

A percentage, from the HR data: 103 of 1,518 June attendance records were Late, 6.8%.

=1.96 * SQRT(0.0679 * (1 - 0.0679) / 1518)     0.0127, or 1.3 percentage points

So the lateness rate is 6.8% ± 1.3 pp: between about 5.5% and 8.1%. One month of data pins it down fairly well.

04Walkthrough

1. Open the legal invoices.csv and add days_to_pay (blank for unpaid invoices), as in lesson 2.
2. Calculate the mean, STDEV.S, COUNT and the margin with CONFIDENCE.T(0.05, …). Write the 95% interval.
3. Recalculate with 0.10 and 0.01 to get the 90% and 99% intervals. Which is widest?
4. Open the HR attendance.csv. Calculate the share of records that are Late and its 95% margin with the proportion formula.
5. In the logistics data, calculate the on-time rate for delivered Air shipments and its 95% interval.
6. Write the sentence you'd give the bank, including what the interval does not cover.

05Practice

06Challenge

07Check your understanding