Odds ratio and relative risk in Excel, with confidence intervals

Set up the 2 × 2 table

Put the groups in the rows, exposed first, and the outcome in the columns, "yes" first. That fixes the letters: a = exposed with the outcome (60), b = exposed without it (340), c = unexposed with the outcome (45), d = unexposed without it (555). Every formula below assumes this layout. Swap a row or a column and the ratio turns into its reciprocal, 0.5 instead of 2.0, which is the most common reason two people get "different" results from the same data. If you start from raw rows, build the four cells with COUNTIFS, as in the chi-square test in Excel guide.

Worked example: back pain in a fictional cohort of 1,000 office workers

Rows 2 and 3 are the 2 × 2 table, cells a and b in row 2, c and d in row 3. Rows 7 to 22 calculate the measures and their 95% confidence intervals.

Worked example: back pain in a fictional cohort of 1,000 office workers

	A	B	C	D
1	Group	Back pain	No back pain	Total
2	Sits 8+ hours (exposed)	60	340	400
3	Sits less (unexposed)	45	555	600
4	Total	105	895	1,000
5
6	Measure			Value
7	Risk in the exposed group a ÷ (a + b)			15.0%
8	Risk in the unexposed group c ÷ (c + d)			7.5%
9	Risk difference			7.5%
10	Relative risk (RR)			2.00
11	Odds in the exposed group a ÷ b			0.176
12	Odds in the unexposed group c ÷ d			0.081
13	Odds ratio (OR)			2.176
14	OR as cross-product (a × d) ÷ (b × c)			2.176
15	z for a 95% interval			1.960
16	SE of LN(RR)			0.1863
17	RR 95% CI, lower			1.388
18	RR 95% CI, upper			2.882
19	SE of LN(OR)			0.2089
20	OR 95% CI, lower			1.445
21	OR 95% CI, upper			3.278
22	Number needed to harm 1 ÷ risk difference			13.3

Fictional data for learning. Educational only, not medical advice.

Relative risk

The risk of each group is the share with the outcome: 60 ÷ 400 = 15.0% and 45 ÷ 600 = 7.5%. The relative risk, or risk ratio, divides them: =D7/D8 = 2.00. Workers who sit 8 hours or more are twice as likely to develop back pain in this cohort. RR = 1 means no difference, below 1 a lower risk in the exposed group.

The risk difference is the absolute effect: 15.0% − 7.5% = 7.5 percentage points. Its reciprocal is the number needed to harm, 1 ÷ 0.075 = 13.3: for about every 13 people exposed, one extra case occurs. In a treatment trial the same number is the number needed to treat. Always report an absolute measure next to a ratio, because a doubling of a rare risk can be a tiny absolute change.

Odds ratio

Odds compare cases with non-cases instead of cases with everyone: 60 ÷ 340 = 0.176 in the exposed group and 45 ÷ 555 = 0.081 in the unexposed group. Their ratio is the odds ratio, =(B2*C3)/(C2*B3) = (60 × 555) ÷ (340 × 45) = 2.176. The OR is always further from 1 than the RR. When the outcome is rare, below about 10%, the two are close, and the OR can be read as an approximate relative risk. When the outcome is common, they drift apart, so do not describe an OR of 3 as "three times as likely".

95% confidence intervals on the log scale

Ratios are skewed: they cannot go below 0 but have no upper limit. So the interval is built for the natural log of the ratio and transformed back with EXP:

• RR: SE of LN(RR) = √(1/a − 1/(a + b) + 1/c − 1/(c + d)) = 0.1863, so the CI is EXP(LN(2.00) ± 1.96 × 0.1863) = 1.388 to 2.882.
• OR: SE of LN(OR) = √(1/a + 1/b + 1/c + 1/d) = 0.2089 (Woolf's method), so the CI is 1.445 to 3.277.

Use NORM.S.INV(0.975) for the 1.96, or 0.995 for a 99% interval. Both intervals exclude 1, so the association is statistically significant at the 5% level. Notice that they are not symmetric around the estimate: 2.00 is 0.61 above the lower limit and 0.88 below the upper one.

When to use RR and when to use OR

Use the relative risk when the data comes from a cohort study or a randomised trial, where you follow groups forward and can measure the risk in each. It is the easier number to explain. Use the odds ratio in a case-control study, where researchers pick people because they have the outcome or not. There the share of cases is set by the design, so a risk cannot be calculated, but the odds ratio is still valid. Logistic regression also reports odds ratios. For a 2 × 2 test of whether the difference could be chance, a chi-square test or, with small counts, Fisher's exact test gives the p-value. For diagnostic tests, the same table is read as sensitivity and specificity.

Zero cells and small samples

If any cell is 0, the odds ratio is 0 or infinite and the standard error divides by zero. The usual fix is the Haldane-Anscombe correction: add 0.5 to all four cells before calculating the OR and its interval, and say so in the report. With very small counts, below about 5 in a cell, the log-scale interval becomes unreliable, and an exact method or Fisher's exact test is the safer choice.

Common mistakes

• A different table layout. With the unexposed group in the first row or "no" in the first column, the same formula gives the reciprocal. Fix the layout first: exposed and outcome "yes" top left.
• Reading the odds ratio as a relative risk. With a common outcome the OR exaggerates the effect. An OR of 2.176 here corresponds to a risk that is 2.00 times higher.
• A symmetric interval. RR ± 1.96 × SE on the ratio itself is wrong. Calculate the SE of the logarithm and transform the limits back with EXP.
• Mixing up the two standard errors. The OR uses 1/a + 1/b + 1/c + 1/d, the RR uses 1/a − 1/(a + b) + 1/c − 1/(c + d).
• Reporting only the ratio. A doubled risk from 0.1% to 0.2% and from 15% to 30% are very different. Add the risk difference or the NNT/NNH.
• Calculating a relative risk in a case-control study. The share of cases is chosen by the design there, so only the odds ratio is meaningful.