Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    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>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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 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. #3
    4 Star Lounger
    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>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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- [(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. #5
    4 Star Lounger
    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>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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