Birthday Code (97/2k)

Topic

New Reply

This probably belongs in the VB/VBA section but since I’m using Excel I’m sure I won’t be too severely admonished.

I have tried, unsuccessfully, to write code which solves the famous birthday paradox. The general solution is given below. I would like to give a worksheet cell the value of the probability from this solution.

In general, the probability that at least 2 out of n people in a given population have the same birthday is: 1 – (364/365 X 363/365 X . . . (365 – n + 1)/ 365).

I’m sure it is fairly easy to do but my attempts have failed to date.
Can anybody help?

Regards

Rob

Reply | Quote

Replies

Array formula:
name a cell “N” (insert name define)
enter a value in “n”

enter this in another cell (confirm with ctrl-shift-enter, not enter)
=1-PRODUCT((366-ROW(INDIRECT(“1:”&n)))/365)

and this will give you the probability.

In a group of 23 (random) people for example the chances are about 50:50 that (at least) 2 will have the same birthday.

Steve

Reply | Quote

Many thanks Steve. As you know, it works a treat. Two points: if you have the time and inclination, would you briefly run through with me exactly what this array formula does? Secondly, I assumed that the code to perform the calculation would be straightforward – my maths and logic isn’t particularly weak, but I am a relative beginner with respect to VBA – yet I am yet unable to accomplish it. Is there something particularly tricky about the task.

Again, many thanks.

Rob

Reply | Quote

The array essentially does your equation:
1- [(366-1)/365 * (366-2)/365 * …. * (366-n)/365]

The 1- is “1-”
The Product function does the multiplication of the Array
The array is (366-ROW(INDIRECT(“1:”&n)))/365)
The rowI(indirect(“1:”&N)) gives you the values 1, 2, …, n

VB is also not hard, just a for… next loop
Steve

Function BDateProb(n As Integer) As Double
    Dim x As Integer
    BDateProb = 1
    For x = 1 To n
        BDateProb = BDateProb * (366 - x) / 365
    Next x
    BDateProb = 1 - BDateProb
End Function

Reply | Quote

Many thanks Steve.
As far as the code goes, I understand the approach and where I was going wrong.
I need to explore array formulae -they seem very powerful yet succinct methods of achieving a variety of solutions. I’ll need to look at each of the functions used and to see what they do.
I find my fumblings useful. I’ve been teaching advanced chemistry for 20 years and it puts me in the same frame of mind as one of my students.

Again, many thanks.

Rob

Reply | Quote

You are welcome.
I have been doing “advanced chemistry for 20 years” and you what they say:
“Chemists have Solutions”
(They also say “Chemists do it with moles” but we won’t go there!)

Steve

Reply | Quote