1. ## Birthday Code (97/2k)

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

2. ## Re: Birthday Code (97/2k)

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

3. ## Re: Birthday Code (97/2k)

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

4. ## Re: Birthday Code (97/2k)

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

<pre>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
</pre>

5. ## Re: Birthday Code (97/2k)

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

6. ## Re: Birthday Code (97/2k)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•