Results 1 to 6 of 6
Thread: Birthday Code (97/2k)

20021119, 19:36 #1
 Join Date
 Sep 2002
 Location
 Stafford, Staffordshire, England
 Posts
 585
 Thanks
 0
 Thanked 0 Times in 0 Posts
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<font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

20021119, 20:29 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 ctrlshiftenter, not enter)
=1PRODUCT((366ROW(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

20021119, 20:46 #3
 Join Date
 Sep 2002
 Location
 Stafford, Staffordshire, England
 Posts
 585
 Thanks
 0
 Thanked 0 Times in 0 Posts
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<font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

20021119, 21:08 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Birthday Code (97/2k)
The array essentially does your equation:
1 [(3661)/365 * (3662)/365 * .... * (366n)/365]
The 1 is "1"
The Product function does the multiplication of the Array
The array is (366ROW(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>

20021119, 22:12 #5
 Join Date
 Sep 2002
 Location
 Stafford, Staffordshire, England
 Posts
 585
 Thanks
 0
 Thanked 0 Times in 0 Posts
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<font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

20021119, 23:33 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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