How to Calculate Odds Ratio and Relative Risk in Excel

This guide will explain how to calculate the odds ratio and relative risk in Excel.

These statistics are useful when determining the relationship between risk factors and outcomes, particularly in medical research.

The odds ratio or OR refers to a measure of association between a particular variable and the likelihood of an event occurring.

Calculating the odds ratio will determine whether some event is more or less likely to occur given some condition. Because of this, odds ratios are often used to identify the relationship between exposure to a risk factor and a medical outcome.

Let’s take a look at a quick example of when to use the odds ratio.

Suppose you want to know the effect of second-hand smoke on respiratory disease. You have a dataset of respondents indicating whether they have a respiratory disease and encounter second-hand smoke in their daily routine.

We can calculate the odds ratio to determine how second-hand smoke affects the likelihood of developing illnesses such as lung cancer.

You may also want to calculate the relative risk of each group instead. The relative risk ratio is the ratio of the risk of an event occurring in one group to the risk of it occurring in another group.

In the next section, we’ll dive deeper into the differences between these two ratios and how to compute for them.

If you want to start calculating the odds ratio and relative risk in Excel, let’s learn how on an actual sample spreadsheet.

 

 

A Real Example of Calculating Odds Ratio and Relative Risk in Excel

The following section provides several examples of how to compute the odds and relative risk ratios. We will also explain the formulas and tools used in these examples.

First, let’s take a look at our sample data. Given the exam results of 100 students, we created a matrix that divides the results by method and whether the student passed or failed.

pass and fail matrix

 

We can find the odds ratio using the following formula:

=(B2/C2)/(B3/C3)

We can also find the relative risk using this formula:

=(B2/(B2+C2))/(B3/(B3+C3))

While the odds ratio can give you a good idea of how a condition can affect an event, you may also want to compute for a confidence interval.

You can compute the confidence interval using the following two formulas:

=EXP(LN(B5)-1.96*SQRT(1/B2+1/C2+1/B3+1/C3))
=EXP(LN(B5)+1.96*SQRT(1/B2+1/C2+1/B3+1/C3))

These two formulas determine the lower and upper limit of the confidence interval of the odds ratio.

Odds Ratio and Relative Risk in Excel

 

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

Use our sample spreadsheet to see how the values change for different sample data.

If you’re ready to try calculating the odds ratio and relative risk, head over to the next section to read our step-by-step breakdown on how to do it!

 

 

How to Calculate Odds Ratio and Relative Risk in Excel

This section will guide you through each step to calculate the odds ratio and relative risk in Excel. You’ll learn how to use different Excel formulas to find the likelihood of an event happening given a certain binary condition.

Follow these steps to find the odds ratio and relative risk ratio function:

  1. First, create a table with the format seen below. The event must have a binary outcome. In the example below, the students may either pass or fail the exam.
    compute for odds ratioWe must first find the ratio of the outcome of each condition. We’ll then divide these two ratios to find the odds ratio.
  2. Next, we’ll calculate the relative risk. We’ll do this by choosing an outcome. For each group, we’ll divide the desired outcome, by the total number of outcomes in the group. Afterwards, we’ll get the ratio of both groups.
    Odds Ratio and Relative Risk in ExcelIn the example above, we’ll first compute the proportion of people who took Method A and passed from the total number of people who took Method A. We’ll perform a similar calculation for the second group.
  3. We can find the confidence interval of the odds ratio by using Excel formulas. We can find the lower limit of the interval by using the formula =EXP(LN(B5)-1.96*SQRT(1/B2+1/C2+1/B3+1/C3)).
    find lower limit of confidence interval
  4. We can find the upper limit of the interval by using the formula =EXP(LN(B5)+1.96*SQRT(1/B2+1/C2+1/B3+1/C3)).
    find confidence interval of odds ratio

These are all the steps you need to perform to calculate the odds ratio and relative risk ratio in Excel. 

 

 

This step-by-step guide should provide you with all the information you need to calculate the odds ratio and relative risk in Excel.

We can use the odds ratio and relative risk ratios to measure the relationship between certain conditions or exposures to some binary outcome.

The odds ratio is just one example of the many statistical methods you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like