Question

It is surprising (but true) that if 23 people are in the same room, there is about a 50% chance that at least two people will have the same birthday. Suppose you want to estimate the probability that if 30 people are in the same room, at least two of them will have the same birthday. You can proceed as follows.

a. Generate random birthdays for 30 different people. Ignoring the possibility of a leap year, each person has a 1/365 chance of having a given birthday (label the days of the year 1 to 365). You can use the RANDBETWEEN function to generate birthdays. What do you expect the average birthday (a number between 1 and 365) among the 30 people be?

b. Once you have generated 30 people's birthdays, how can you tell whether at least two people have the same birthday? One way is to use Excel's RANK function. (You can learn how to use this function in Excel's online help.) This function returns the rank of a number relative to a given group of numbers. In the case of a tie, two numbers are given the same rank. For example, if the set of numbers is 4, 3, 2, 5, the RANK function returns 2, 3, 4, 1. (By default, RANK gives 1 to the largest number.) If the set of numbers is 4, 3, 2, 4, the RANK function returns 1, 3, 4, 1. What do you expect the sum of the birthday ranks for the 30 people be, if there are no two people with the same birthday?

Answer 1

a) To set up this simulation, create a spreadsheet with the formulas as shown below:

Drag the values in row 10 down to row 34 (generating 30 different people)

b) In cell C5, enter this formula

=RANK(B5.$B$5:$B$34)
Drag this formula down to C34

To determine if there is at least one match, we enter new columns as shown here

Drag these down until all ranks (1-30) are represented. This will count the number of times any
particular rank occurs.

Now, we create an output cell, by placing in the spreadsheet the following formula:

Match?
=IF(MAX{FS:F34)>1,1,0)

This will give a "1" if there is a match. Make this a Risk output cell.

Running 1,000 iterations produces the following:

Name	mean
Match?	0.68

With 30 people, we have about a 2/3 chance to have at least one matching birthday