Results 1 to 12 of 12

RANDBETWEEN - maybe not so random

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-30-2010
    Location
    Denmark
    MS-Off Ver
    Office 365
    Posts
    215

    RANDBETWEEN - maybe not so random

    I have not gone into details with the statistic calculation here, but im quite puzzled by the following.

    I need to generate usernames with 2 letters (from A-Z), followed by 2 numbers (from 0-9) and then again 2 letters, for example AB45DE.

    I made a formula to do that:
    =CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(0,9)&RANDBETWEEN(0,9)&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
    Im not much into the calculations of likelihood, but this should have more than 45 million combinations, ie (26*26*10*10*26*26)=45.697.600

    I then do this for 10.000 rows, hoping for 10.000 unique usernames. But what happens more often than not is that I end up with 1, 2, 3 or more duplicates in the 10.000 rows. This seems very strange and unlikely to me.

    I made a sheet to illustrate this - its generates the names in column A and shows if its duplicated in column B (2 or more is a duplicate), and the sum of duplicates in D1 - it runs by clicking the Randomize button (takes a little while to run) and it converts to values at the end so it will stay the same until next run. Try running it a few times and you will get duplicates.

    Can it really be statistically correct that you get duplicates so often in 10.000 rows, outta 45 million possible combinations or is the Excel RANDBETWEEN really not that random after all?
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Assign random number (RANDBETWEEN) equal number of times
    By dechesne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-20-2016, 10:58 AM
  2. Replies: 10
    Last Post: 07-03-2015, 04:29 PM
  3. HELP: How to get random data from a 'table' using 'randbetween'
    By faizzsheikh in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2015, 08:03 PM
  4. Randbetween or Random Number (1,3)
    By itselflearn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2013, 02:34 PM
  5. Replies: 8
    Last Post: 09-21-2011, 06:50 PM
  6. Replies: 2
    Last Post: 09-13-2005, 12:05 PM
  7. Random numbers not using randbetween()
    By davehill1974 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-07-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1