The Hardy-Weinberg equation in Excel, with worked examples

The two Hardy-Weinberg equations

p + q = 1 for the alleles, and p² + 2pq + q² = 1 for the genotypes.

The second equation is simply (p + q)² written out. If a population mates at random, an individual gets one allele from each parent, so the chance of two M alleles is p × p, of two N alleles q × q, and of one of each 2pq, because the M can come from either parent. The equations hold only under the Hardy-Weinberg conditions: random mating, no selection, no mutation, no migration and a population large enough that chance (genetic drift) does not shift the frequencies.

Step 1: allele frequencies from genotype counts

When heterozygotes can be told apart, as in the codominant MN blood group, count alleles directly. Each person carries two alleles, so 400 people carry 800. MM people carry two M alleles and MN people one:

p = (2 × MM + MN) ÷ 2n = (2 × 128 + 184) ÷ 800 = 0.55, and q = 1 − p = 0.45.

Step 2: expected genotype frequencies and counts

Under Hardy-Weinberg, MM should make up p² = 0.3025 of the population, MN 2pq = 0.4950 and NN q² = 0.2025. The three always add up to 1, which is a quick check of the formulas. Multiplied by 400 people, the expected counts are 121, 198 and 81, against 128, 184 and 88 observed. There are slightly fewer heterozygotes than expected. Is that chance, or a sign that the population is not in equilibrium?

Worked example: MN blood group and a recessive condition

Observed genotype counts and the incidence are inputs (yellow). Rows 2 to 5 compare observed with expected genotypes, rows 8 to 14 hold the allele frequencies and the test, rows 17 to 22 the carrier estimate. The sample is illustrative.

Worked example: MN blood group and a recessive condition

	A	B	C	D	E
1	Genotype	Observed	Expected freq.	Expected count	(O − E)² ÷ E
2	MM	128	0.3025	121.0	0.405
3	MN	184	0.4950	198.0	0.990
4	NN	88	0.2025	81.0	0.605
5	Total	400	1.0000	400.0	2.000
6
7	Allele frequencies and test	Value
8	Alleles in the sample (2n)	800
9	p = frequency of M	0.550
10	q = frequency of N	0.450
11	χ² statistic	2.000
12	Degrees of freedom (3 − 1 − 1)	1
13	p-value	0.157
14	Critical value at α = 0.05	3.841
15
16	Recessive condition	Value
17	Affected newborns: 1 in	10,000
18	q² = incidence	0.0001
19	q = SQRT(q²)	0.010
20	p = 1 − q	0.990
21	Carrier frequency 2pq	1.98%
22	Carriers: 1 in	50.5

Educational example with illustrative numbers, not medical or genetic-counselling advice.

Step 3: a chi-square test

For each genotype, calculate (observed − expected)² ÷ expected and add them up: 0.405 + 0.990 + 0.605 gives χ² = 2.000. The degrees of freedom are not 3 − 1 = 2 here: one more is lost because p was estimated from the same data, so df = 3 − 1 − 1 = 1. Then =CHISQ.DIST.RT(2.000,1) returns a p-value of 0.157. That is above 0.05, and χ² is below the critical value of 3.841, so the data are consistent with Hardy-Weinberg equilibrium. That does not prove equilibrium. It only means the deviation is small enough to be chance.

Step 4: carrier frequency of a recessive condition

With a recessive condition you cannot see carriers, but you can count affected people, who are the q² group. If a condition affects 1 in 10,000 newborns, then q² = 0.0001 and q = SQRT(0.0001) = 0.01. So p = 0.99 and the carrier frequency is 2pq = 2 × 0.99 × 0.01 = 1.98%, about 1 in 50.5 people. A condition that looks very rare is carried by around 200 times as many people as it affects. This estimate assumes equilibrium, which close relatives marrying or strong selection can break.

When the dominant phenotype hides the heterozygotes

With complete dominance, AA and Aa look the same, so you cannot count alleles directly. Start from the recessive phenotype instead: its share is q², so q = √(share), and then p = 1 − q, as in step 4. This is the usual route in exam questions such as "16% of a population shows the recessive trait": q² = 0.16, q = 0.4, p = 0.6, and 2pq = 48% are heterozygous.

Common mistakes

• Counting people instead of alleles. p is a share of alleles, so divide by 2n, and count heterozygotes once and homozygotes twice.
• Using 2pq as "two times p". 2pq is 2 × p × q. In Excel write =2*B9*B10.
• Taking q as the recessive phenotype share. The share of affected individuals is q², so q is its square root.
• Wrong degrees of freedom. With three genotypes and p estimated from the data, df = 1, not 2. With df = 2 the p-value is too large.
• Percentages in the chi-square test. Use counts, never percentages, and make sure every expected count is at least 5.
• Reading a large p-value as proof. Not rejecting Hardy-Weinberg only means the data do not show a clear deviation.