Raking survey weights in Excel: iterative proportional fitting step by step

When you need raking instead of cell weighting

Post-stratification (cell weighting) divides the population count of each cell, say men aged 18–34, by the sample count of the same cell. It needs the joint population table. Often you only have the margins: the age distribution from one source and the gender split from another. Raking finds weights that hit every margin at the same time using only those totals, and it scales to many variables where a joint table would have hundreds of small or empty cells.

Step 1: set the targets and fit the first margin

Turn the population shares into targets on the scale of the sample: 30%, 34% and 36% of 1,000 interviews give age targets of 300, 340 and 360, and 49% / 51% give gender targets of 490 and 510. The sample has too few young people (220) and too many aged 55+ (440).

Fit the rows first: multiply every cell by its row target ÷ row sum. In Excel =B2*$E2/$D2, copied across the block. Young men go from 90 to 122.73, and each age row now adds up exactly to its target. The gender columns, 411.82 and 588.18, are still off.

Step 2: fit the second margin, then repeat

Now fit the columns: multiply every cell of the step 1 block by its column target ÷ column sum, =B8*B$6/B$11. The columns hit 490 and 510 exactly, but the rows move a little (35–54 is now 343.22 instead of 340). Fitting the rows again in step 3 brings them back, and the column gap shrinks to 0.18 of 1,000 interviews.

Each pass disturbs the previous margin less, so IPF converges quickly when no cell is empty. Real raking stops when the largest gap falls below a tolerance such as 0.001, usually after a handful of iterations. With more variables you simply fit each margin in turn inside every iteration.

Worked example: raking 1,000 interviews to age and gender

The interview counts and the targets are yellow. Each block fits one margin: step 1 the age rows, step 2 the gender columns, step 3 the age rows again. The data are synthetic.

Worked example: raking 1,000 interviews to age and gender

	A	B	C	D	E
1	Start: interviews	Men	Women	Row sum	Age target
2	18–34	90	130	220	300
3	35–54	150	190	340	340
4	55+	170	270	440	360
5	Column sum	410	590	1,000	1,000
6	Gender target	490	510	1,000
7	Step 1: fit age rows	Men	Women	Row sum	Age target
8	18–34	122.73	177.27	300.00	300
9	35–54	150.00	190.00	340.00	340
10	55+	139.09	220.91	360.00	360
11	Column sum	411.82	588.18	1,000.00
12	Step 2: fit gender columns	Men	Women	Row sum	Age target
13	18–34	146.03	153.71	299.74	300
14	35–54	178.48	164.74	343.22	340
15	55+	165.50	191.55	357.04	360
16	Column sum	490.00	510.00	1,000.00
17	Step 3: fit age rows	Men	Women	Row sum	Age target
18	18–34	146.16	153.84	300.00	300
19	35–54	176.80	163.20	340.00	340
20	55+	166.87	193.13	360.00	360
21	Column sum	489.82	510.18	1,000.00
22	Raked weight (step 3 ÷ start)	Men	Women
23	18–34	1.624	1.183
24	35–54	1.179	0.859
25	55+	0.982	0.715
26	Largest gap to the gender target	0.18
27	Max ÷ min weight	2.27x

Synthetic data for practice. Real surveys rake on the respondent file with more variables and a convergence tolerance.

From raked counts to respondent weights

The weight of every respondent in a cell is the raked count ÷ the original count: =B18/B2. Young men get 1.624 (each counts for 1.6 respondents), older women 0.715. The weights still add up to 1,000, so the weighted sample size is unchanged.

On a respondent file you do the same with SUMIF instead of a table: for each variable, compute the weighted total per category with SUMIF on the current weights, then multiply each respondent's weight by target ÷ weighted total for their category, and repeat. Survey software runs exactly this loop.

Check the weights: spread, trimming and the design effect

Weights cost precision. The ratio of the largest to the smallest weight is 2.27 here, which is mild. Kish's design effect, n × Σw² ÷ (Σw)², is about 1.07, so the 1,000 weighted interviews carry about as much information as 935 unweighted ones. When a few cells get very large weights, analysts trim them to a cap and rake again. Report the margin of error with the design effect, see margin of error in Excel.

Common mistakes

• Multiplying all adjustment factors at once. Raking fits one margin at a time and uses the result of the previous step. One combined factor does not hit any margin exactly.
• Targets that do not add up to the same total. Every margin must sum to the sample size (or the population), otherwise IPF cannot converge.
• Stopping after one pass. After the last margin is fitted the earlier ones are off again. Repeat until the largest gap is below a tolerance.
• Empty or tiny cells. A cell with no respondents can never be weighted up, and a cell with very few respondents gets extreme weights. Collapse categories first.
• Never checking the spread of the weights. Look at the max ÷ min ratio and trim extreme weights before you publish estimates.
• Reporting the unweighted margin of error. Weighting raises the variance. Use the design effect or the effective sample size.