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))
Next
Range("B8").Value2 = (1 - p) * 100
End Sub
Template looks like this:
Download file: probability-of-infection-in-group.xlsm
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
The for loop in VBA could be done without macro. In this case the PRODUCT() function will do the job.
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.
WOW,
I didn’t expect these numbers. It is better to do math than to believe in intuition. Thank you.