RANDBETWEEN - maybe not so random

Two things to note. First, software does not generate random numbers. It generates pseudo-random numbers. This means, roughly, that a sequence of numbers should be intractably hard to predict and should approximate a random distribution, but is not the same as true randomness. This is a pretty big topic and I am not a mathematician so am limiting my explanation here.

Second, even with true random numbers, if you generate 10,000 numbers at random with replacement from a population of 45,697,600, the chances are, by rough calculation, at least 1/9 of having at least two duplicates in that list. (This is a bit back-of-the-envelope but I'll show my work if you're really interested.)

If you need to generate 10,000 strings that are guaranteed to be unique then I would use VBA code.

Originally Posted by 6StringJazzer

This is a bit back-of-the-envelope but I'll show my work if you're really interested.

I am... and would be interested to see... Since I'm not the OP, don't bother if it's going to be a lengthy discourse!!

Originally Posted by Glenn Kennedy

I am... and would be interested to see... Since I'm not the OP, don't bother if it's going to be a lengthy discourse!!

In writing out my answer I found an error so let's wait on that


But ben_hensel said it well--sometimes math clashes with intuition. If you have 23 randomly chosen people, chances are 50% that you'll get at least one duplicate birthday.

Forget it. I was asleep. The first letter and digit don't count. So it's 26*26*26*10

RAND(), and by extension RANDBETWEEN(), kind of have a reputation for being not all that great. You can read the gossip.

Specifically to your case though,
This is in the same family as the Birthday problem of the probability that random events/numbers will duplicate. (The short answer is that duplicates are more likely than naive intuition would assume.)

If you just want to generate a bunch of mostly-random usernames as a one-off, then I would tell you to generate extras, and then just cull the duplicates out.

Hi Imbizile,

Random numbers don't mean they won't repeat. If you think of a 6 side die and you role it twice, will you get the same number again (at random)? Of course you would. Just because you have 45 million possible sides on the die, doesn't mean it won't repeat.

Now if you want to insure you get unique names, simply filter your B column, in your example, by 1 so any duplicate counts won't show. Click your button and sort column B from Large to Small. Then drop down the B column filter and unselect the 2. You have a good answer then, unique random names.

The probability of picking 10,000 distinct items when multi-selecting from 45,697,600 is ...

{=PRODUCT(1 - (ROW(INDIRECT("1:10000")) - 1)/45697600)} ~ 33%

You should see the same result if you enter =randbetween(1, 45697600) down 10,000 rows.

If you just generate a few extra values they way you are, you could copy, paste as values, and then do Remove Duplicates.

Odds of 1 or more duplicates in 10000 rows : 66.52 %
(The loop calcs the probability of 10000 uniques, which is then subtracted from 1.)

Sub CalcOdds()
    Dim M As Double, i As Double, k As Double
    M = 26 ^ 4 * 10 ^ 2
    k = 1
    For i = 0 To 9999
    k = k * (1 - i / M)
    Next
    MsgBox "Odds of 1 or more duplicates in 10000 rows: " & Format(100 * (1 - k), "0.00") & "%"
End Sub

Hi Imbizele,

I'm smiling when you wrote:

I believe the 66,52% probability is the correct one.

shg (who is rarely wrong) said the probability of picking 10000 distinct items while leelnich said "1 or more duplicates". I think they may both be right.

I used to teach statitistics and there were two types of problems where reading the problem was imperitive. It was putting black and white marbles in a bag and drawing them out. One problem was the probability of drawing marbles out "with replacements" and the other was with NO replacements. Many time students would confuse the two types of games. Reread both answers above and see if they might both be correct.