Lets say our number of people in the group is in A1, then the (Array) formula is this (it will give the wrong answer if not entered as CSE)
=1-PRODUCT(ROW(INDIRECT(“365:”&366-A1)))/POWER(365,A1)
This gives the probability of .507 of at least 2 people out of 23 sharing a birthday.
I posted this as a challenge on the Excel Hero Linked In forum, and got back a nice solution from Kevin Wright, which of course doesn’t need to be entered as an array formula.
=1-PERMUT(365,A1)/365^A1
In trying to generalize the problem – say 3 or 4 or … k people with shared birthdays in a group of n, I came up with a partial solution
=1-PERMUT(365,A1)/365^A1-COMBIN(A1,B1-1)*PERMUT(365,A1-1)/365^A1
A sample cell contains this formula
=IFERROR(COMBIN(365,G$1)*PERMUT($A23,2*G$1)*PERMUT(365-G$1,$A23-G$1*2)/(2^G$1*365^$A23),””)
Hans Knudsen translated this into an array formula, doing away with the need for a table, as below (group size is in a8)
=1-SUM(COMBIN(365, (ROW(INDIRECT(“1:”&TRUNC(A8/2)))-1))*PERMUT(A8,2* (ROW(INDIRECT(“1:”&TRUNC(A8/2)))-1))*PERMUT(365- (ROW(INDIRECT(“1:”&TRUNC(A8/2)))-1),A8-2* (ROW(INDIRECT(“1:”&TRUNC(A8/2)))-1))/((2^ (ROW(INDIRECT(“1:”&TRUNC(A8/2)))-1)*365^A8)))
In the meantime why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines