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.

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))

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

Template looks like this:


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

Add a comment »5 comments to this article

  1. This is exactly what I have looking for. Infection probability calculator in Excel. Is there any change to generate statistics from this results? I need overview of number of infected people progression in time. It will be great to have this data in chart.

    Regards Laura


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


  3. Hello,

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


    • 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.


  4. WOW,

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


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