Birthday Paradox Simulation

[davery5872]

I am trying to set up a simulation for the birthday paradox. I have random dates set up, but cannot figure out how to sort (so I can count duplicates). Every time I hit sort, it generates another batch of random numbers. Or is there a way I can set this up?

Thanks for the help.

 

[Airfix9]

Random functions are volatile. The only thing you can do is to set up the random dates, copy & paste (special.. values) them to fix them and then sort.

 

[xenou]

You can also use the data analysis toolpak (addin) to generate random numbers that won't be formulas (I've done it a few times - short term it's probably easier to just hard copy the values as stated ... you might put a "live formula" in a row above your data so you can easily copy it down and "refresh" your random number list without having to retype the formula.

 

[pgc01]

Hi
Welcome to the board

You can use a dynamic solution that's very easy to implement. You can generate a new draw simply by hitting F9 and so by hitting repeatedly F9 you can experiment with the birthday paradox.

For ex., for draws of 30 numbers (from the set of numbers 1 to 365, representing the days of the year)

In A1: =1+INT(RAND()*365)
In C1: =SMALL($A$1:$A$30,ROW())
In D1: =IF(C1=C2,"True","")

Copy the formulas till row 30.

Hit F9 repeatedly. Whenever a number in the draw repeats you get a True in column D.

Have fun.

 

[sulakvea]

hi, if the issue is just counting the duplicates, you dont need to sort the set

say your rand numbers sit in A1:A30. in B1, type

=SUM(COUNTIF(A1:A30,A1:A30))-30

where 30 is the number of entries. you can change it based on how many numbers you have. enter this formula as array, i.e. with ctrl+shift+enter. it will give you number of duplicates. you can also add >=1 piece to it so it just gives you TRUE or FALSE. smth like, again entered as array formula

=SUM(COUNTIF(A1:A30,A1:A30))-30>=1