Sample size for comparing two means: power analysis in Excel

The sample size formula for two means

n per group = 2 × (zα/2 + zβ)² × σ² ÷ Δ²

• α is the significance level, the risk of a false alarm, usually 5%.
• Power = 1 − β is the chance of detecting a real difference of size Δ, usually 80% or 90%.
• σ is the standard deviation of the outcome within a group.
• Δ is the smallest difference between the group means that would matter.

The factor 2 is there because both groups add noise to the difference between their means. The result is per group: the study needs twice as many in total.

The z values with NORM.S.INV

For a two-sided test at α = 5%, the critical value is =NORM.S.INV(1-B2/2) = 1.960. The power term is =NORM.S.INV(B3) = 0.842 for 80% power. With σ = 6 and Δ = 2.5: 2 × (1.960 + 0.842)² × 36 ÷ 6.25 = 90.42, and =ROUNDUP(B12,0) gives 91 per group, 182 in total. Always round up: rounding down leaves the study just short of its power.

A one-sided test uses NORM.S.INV(1-α) = 1.645 and needs fewer people, but only if you decided in advance that a difference in the other direction does not interest you.

Worked example: sample size for a training study

A contact centre compares call handling time after an old and a new training programme. The inputs (yellow) are α, power, σ, Δ and the expected dropout. Rows 9 to 17 calculate the sample size step by step, rows 20 to 23 show n for other effect sizes.

Worked example: sample size for a training study

	A	B
1	Input	Value
2	Significance level α (two-sided)	5%
3	Power (1 − β)	80%
4	Standard deviation σ (minutes)	6.0
5	Difference to detect Δ (minutes)	2.5
6	Expected dropout	10%
7
8	Calculation	Result
9	Effect size d = Δ ÷ σ	0.417
10	z for α/2	1.960
11	z for power	0.842
12	n per group (unrounded)	90.42
13	n per group	91
14	n per group with t correction	92
15	Total participants	184
16	Recruit per group (dropout)	103
17	Check: power with B14 per group	80.7%
18
19	Effect size d	n per group, 80% power
20	0.20	394
21	0.50	64
22	0.80	26
23	n per group at 90% power (d of B9)	123

Normal approximation with a simple t correction, two-sided test, equal groups, illustrative inputs. Educational only, not a substitute for a statistician on a real study.

Effect size: Cohen's d

Dividing Δ by σ gives the standardised effect size Cohen's d: 2.5 ÷ 6 = 0.417. The formula then simplifies to n = 2 × (zα/2 + zβ)² ÷ d², which needs no units at all. Cohen's rough labels are 0.2 small, 0.5 medium and 0.8 large.

The table in the example shows how fast small effects get expensive: at 80% power and α = 5%, with the t correction below, d = 0.8 needs 26 per group, d = 0.5 needs 64 and d = 0.2 needs 394. Because d is squared, halving the effect quadruples the sample.

From the normal formula to the t-test

The formula uses the normal distribution, but the analysis will be a t-test, which needs slightly more data. A simple correction adds zα/2² ÷ 4, about one participant per group at α = 5%: =ROUNDUP(B12+B10^2/4,0) gives 92 per group, 184 in total. Exact t-based software usually agrees within one participant.

To check the plan, calculate the power the sample actually delivers: =NORM.S.DIST(d*SQRT(n/2)-zα/2,TRUE) returns 80.7% for 92 per group.

Dropout, more power and unequal groups

• Dropout: if you expect 10% to drop out, recruit =ROUNDUP(B14/(1-B6),0) = 103 per group, so that 92 complete the study.
• More power: 90% power with the same effect needs 123 per group instead of 92.
• Unequal groups: a 2:1 split needs more people in total than a 1:1 split for the same power. Keep groups equal unless one arm is much cheaper.

Comparing two proportions, such as conversion rates, uses a related formula: see the A/B test sample size calculator. For estimating a single share in a survey, start from the margin of error.

Where σ and Δ come from

The standard deviation comes from earlier data, a pilot study or published research. A small pilot gives a noisy estimate, so check how the sample size changes if σ is 10% to 20% higher. Δ should be the smallest difference that would change a decision, not the difference you hope for. Powering a study for an optimistic effect from a pilot is the most common reason studies end up underpowered.

Common mistakes

• Forgetting the factor 2. Both groups add variance to the difference. Without the 2 the sample is half of what is needed.
• Leaving out the power term. With only zα/2 in the formula, the study has just 50% power.
• Reporting the per-group n as the total. 92 per group means 184 participants, before dropout.
• Rounding down. Sample sizes are minimums, so use ROUNDUP.
• Using a one-sided z without a reason. It shrinks the sample but ignores effects in the other direction. Decide before the study, not after.
• Planning with the pilot's effect size. Small pilots often overstate the effect. Use the smallest difference that matters.