How to Calculate Odds Ratio in Excel

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to calculate the Odds Ratio in Excel. The Odds Ratio (OR) can be defined as the probability of occurrence of an event in proportion to the likelihood of non-occurrence of that event.

Consider the following generic dataset.

Here the odds of the event happening in the treatment group is a/b. Similarly, the odds of the event happening in the control group is c/d. So, the ratio of the two odds will be (a/b)/(c/d).

Therefore, the generic formula for the odds ratio is as follows.

Odds Ratio = (a/b)/(c/d) = (a×d)/(b×c)


How to Calculate Odds Ratio (OR) in Excel

Assume you have the following dataset. It contains the results from a medical survey. The survey was done on some intravenous drug users to find out if they were HIV+ or HIV-. And then it was done on people who didn’t use the drugs.

Now, enter the following formula in cell E5 to calculate the odds ratio of the survey results.

=(C5/D5)/(C6/D6)

Calculate Odds Ratio in Excel

Interpretation:

Here, the result 2.14 indicates that it is 2.14 times more likely for those who are HIV+ to also be IVD+ than to be IVD-.

Read More: How to Calculate Ratio of 3 Numbers in Excel


Calculate Confidence Intervals (CI) for Odds Ratio in Excel

The generic formula for the confidence intervals for the odds ratio is as follows.

Confidence Interval = e^(ln(OR)±1.96×√(1/a+1/b+1/c+1/d))

  • Now, enter the following formula in cell F5 to get the lower 95% confidence interval. The formula contains the EXP, LN, and SQRT functions.
=EXP(LN(E5)-1.96*SQRT(1/C5+1/D5+1/C6+1/D6))

Calculate Confidence Intervals (CI) for Odds Ratio

  • Then enter the following formula in cell G5 to get the upper 95% confidence interval.
=EXP(LN(E5)+1.96*SQRT(1/C5+1/D5+1/C6+1/D6))

Calculate Confidence Intervals (CI) for Odds Ratio


Things to Remember

  • You should not conclude from the odds ratio that IV drug users are 2.14 times more likely to become HIV+ than those who do not use the drugs. Rather you should interpret it the other way around.
  • OR>1 generally indicates a higher likelihood for an event to occur than to not occur. OR<1 indicates the opposite.

Download Sample Workbook


Conclusion

Now you know how to calculate the odds ratio in Excel. Did this article help you with the task? If you have further queries or suggestions, please let us know in the comment section below. Stay with us and keep learning.


Related Articles


<< Go Back to Ratio in Excel | Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo