Every time I post code that includes picking a random integer, Tushar reminds me that my methods produce biased results. The last time, I swore to myself I would do it right. Here’s how the old me would have done it:

Sub RandTest1()

Dim lRand As Long

Dim i As Long

Dim aResult() As Long

Dim dLow As Double

Dim dHigh As Double

dLow = 1

dHigh = 4

ReDim aResult(dLow To dHigh) As Long

For i = 1 To 10000

lRand = Round(Rnd * (dHigh – dLow) + dLow, 0)

aResult(lRand) = aResult(lRand) + 1

Next i

For i = 1 To 4

Debug.Print i & “: “ & aResult(i)

Next i

End Sub

Dim lRand As Long

Dim i As Long

Dim aResult() As Long

Dim dLow As Double

Dim dHigh As Double

dLow = 1

dHigh = 4

ReDim aResult(dLow To dHigh) As Long

For i = 1 To 10000

lRand = Round(Rnd * (dHigh – dLow) + dLow, 0)

aResult(lRand) = aResult(lRand) + 1

Next i

For i = 1 To 4

Debug.Print i & “: “ & aResult(i)

Next i

End Sub

Because I’m using Round(), it will only pick the edges ~50% as often as the middle numbers. It seems that if I simply increase my range, that is reduce dLow by 0.5 and increase dHigh by .49, my distribution will be right. Technically it won’t be right, but it will be close enough for government work. Whereas the old method wasn’t even close enough for that.

Sub RandTest2()

Dim lRand As Long

Dim i As Long

Dim aResult() As Long

Dim dLow As Double

Dim dHigh As Double

dLow = 1

dHigh = 4

ReDim aResult(dLow To dHigh) As Long

dLow = dLow – 0.5

dHigh = dHigh + 0.49

For i = 1 To 10000

lRand = Round(Rnd * (dHigh – dLow) + dLow, 0)

aResult(lRand) = aResult(lRand) + 1

Next i

For i = 1 To 4

Debug.Print i & “: “ & aResult(i)

Next i

End Sub

Dim lRand As Long

Dim i As Long

Dim aResult() As Long

Dim dLow As Double

Dim dHigh As Double

dLow = 1

dHigh = 4

ReDim aResult(dLow To dHigh) As Long

dLow = dLow – 0.5

dHigh = dHigh + 0.49

For i = 1 To 10000

lRand = Round(Rnd * (dHigh – dLow) + dLow, 0)

aResult(lRand) = aResult(lRand) + 1

Next i

For i = 1 To 4

Debug.Print i & “: “ & aResult(i)

Next i

End Sub

Here’s the results from running both

Looks like a winner to me. Am I missing anything?

Why not just truncate a text string and pass it back to an int? – am I missing something ;-)

Why not use the RandBetween worksheet function?

This is why numeric programming isn’t trivial, no matter how easy it may seem.

Don’t round, truncate.

f = lo + Int(Rnd * (hi – lo + 1))

End Function

Putting this differently, is

R = Round(Rnd * (H – L) + L, 0)

really clearer than

R = L + Int(Rnd * (H – L + 1))

?

I suppose the ‘+ 1’ may not be immediately obvious. It arises from there being only N – 1 intervals bounded by N distinct boundary points. This N – 1 to N relationship between intervals and boundary points is fundamental to numeric programming.

Sub RandTest2() is still biased. The upper bound will lack favor by about 1% in long runs, owing to the lack of precision in adding 0.49. Other evils creep in too, such as lRand = 0. Adjusting the precision of the offset seems futile. Not sure if that’s close enough for the guv?

fzz’s is the way I’ve done it since way back, Apple //e days.

You are indeed missing something!

With RandTest2 you will get

a) value 4 with probability (0.99/3.99)=24.812%; and

b) values 1..3 with probability (each)25.0627%

so not an accuate uniform distribution

Best to use ‘int’ rather than ’round’. Try this instead:

Sub RandTest3()

Dim lRand As Long

Dim i As Long

Dim aResult() As Long

Dim dLow As Double

Dim dHigh As Double

dLow = 1

dHigh = 4

ReDim aResult(dLow – 1 To dHigh) As Long

dLow = dLow

dHigh = dHigh

For i = 1 To 10000

lRand = Int(Rnd * (dHigh – dLow + 1) + dLow)

aResult(lRand) = aResult(lRand) + 1

Next i

For i = 1 To 4

Debug.Print i & “: ” & aResult(i)

Next i

End Sub

Dick –

fzz beat me to it, and Ross hinted at it first.

Your second procedure is essentially calculating Low + Int(High – Low + 0.99)

For an accountant, isn’t that somewhat precise? I guess if you’re an accountant for Enron, it’s orders of magnitude too precise.

So, I’m really curious now. Why go through all the trouble of creating a random function when one exists? Is the built in function RANDBETWEEN biased? Are there advantages to building your own?

Speed is one big advantage to writing your own. A few quick tests on my machine showed that my random function (like fzz’s) is about 30-50 times faster than using the RandBetween Worksheetfunction. Also, previous to 2007 (at least in 2003), you needed to have the Analysis Tool Pak installed for RandBetween. Writing your own function helps improve compatibility.

-Josh

@JoshG

Thanks for the explanation. Good stuff to know.

According to “Excel for Scientists and Engineers” (E. Joseph Billo) a new algorithm for random numbers was introduced in Excel 2003, because the old one wasn’t very good. Does anyone know if VBA uses the new algorithm or the old one (or something different)?

they’re all biased! use this: http://www.random.org/clients/http/

Dick wrote: “Because I’m using Round(), it will only pick the edges ~50% as often as the middle numbers. It seems that if I simply increase my range, that is reduce dLow by 0.5 and increase dHigh by .49, my distribution will be right. Technically it won’t be right, but it will be close enough for government work. Whereas the old method wasn’t even close enough for that.”

So, why not just use INT in Excel or Int in VBA and get the correct result in the first place — subject, of course, to the limitations in RAND (or Rnd). It’s a lot easier than trying to fudge ROUND/Round. {grin}

Hey,

Does this give unique random or there are repeats ??

I am looking @ creating a Quizz which pick unique random questions from different sections,however this below picks up repeats tooo

For j = 1 To SA

RD = WorksheetFunction.RandBetween(2, ACount)

Worksheets(“Quiz Paper”).Cells(j + 10, 1) = Worksheets(“Section A”).Cells(RD, 1)

Worksheets(“Quiz Paper”).Cells(j + 10, 2) = Worksheets(“Section A”).Cells(RD, 2)

Worksheets(“Quiz Paper”).Cells(j + 10, 3) = “Section A”

Worksheets(“Quiz Paper”).Cells(j + 10, 4) = Worksheets(“Section A”).Cells(RD, 3)

Next j

Busybee:

See

Select elements at random without repetition

http://www.tushar-mehta.com/excel/newsgroups/rand_selection/

@Tushar

I did go thru the page.. am just confused newbie…will it work as a worksheet function ?

I am just not sure which is one is right for me :-(

Thx in advance.