Calculate infection probability in group of people

Imagine that you want to arrange a meeting or birthday party for 40 people in the area where are infected people. What is the chance that one of them will come to your event? This probability can be calculated.

I created simple Excel macro to do the math. It uses birthday paradox rule. More information and web-based tool can be found here Covid area – infection probability calculator

You have to know population in the area and number of currently infected people in this area.


Sub covid()
  totalPopulation = Range("B1").Value2
  activeCovidCases = Range("B2").Value2
  groupSize = Range("B3").Value2
  p = 1
  For i = 0 To groupSize - 1
    p = p * ((totalPopulation - i - activeCovidCases) / (totalPopulation - i))
  Next

  Range("B8").Value2 = (1 - p) * 100
End Sub


Template looks like this:

covid-group-area

Download file: probability-of-infection-in-group.xlsm

Add a comment »4 comments to this article

  1. The for loop in VBA could be done without macro. In this case the PRODUCT() function will do the job.

    Reply

  2. Hello,

    is this macro or formula applicable for all diseases or just a COVID-19 infection?

    Reply

    • Yes,

      this procedure has universal usage. It is general formula for probability calculation. It can be used for Covid infection calculation or any other diseases or whatever. For example what is the probability of having blue car in your neighbourhood. But it is necessary to know all base numbers which will be used in the function.

      Reply

  3. WOW,

    I didn’t expect these numbers. It is better to do math than to believe in intuition. Thank you.

    Reply

Leave a Reply to Petra Cancel reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress