Random Numbers

[smandula]

I would like to generate random numbers between 1 and 100,
to list only the top 20 numbers, A1.. A20
doing this after a 1,000 interations

In Excel VBA

With Thanks

 

[JP Ronse]

Hi,

You can use the RND function in VBA, see the help on RND

Sub test()
Dim i As Integer
For i = 1 To 1000
Debug.Print Int((100 - 1 + 1) * Rnd + 1)
Next
End Sub

You can have also a look on Dermot Balson pages:

http://www.westnet.net.au/balson/ModellingExcel/PowerTools.shtml

Your second question is not clear to me, what do you mean with the top 20
numbers? 20 most frequently generated numbers?

Wkr,

JP

 

[smandula]

Hello,

Your second question is not clear to me, what do you mean with the top 20
numbers?  20 most frequently generated numbers?

Thanks for your reply.

When a 1,000 random numbers are displayed, using a 1,000 rows,
take the frequency of the 1000 numbers and display on the top 20
most frequent numbers,different numbers. I suspect some numbers
would be repeated several times. Thereby, using only 20 rows not 1,000
rows

With thanks

 

[JP Ronse]

Hi,

I was thinking further on this but do not see a fast way do do it in VBA
only. The code below is creating 1000 random numbers in column A, in column
B & C, you will find the unique randoms numbers (B) and the number of
occurences (C).

As I don't know how your workbook is set-up, I'm a bit cautious to supply
code that could remove contents of your workbook.

You can start recording a macro and add the rank formula in D. Sort B-D and
take top 20.

Please let us know if this was helpfull.

Wkr,
JP

Sub Rand()
Dim i As Integer, j As Integer
Dim arr(1 To 1000, 1 To 2)

Randomize
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

For i = 1 To 1000
arr(i, 1) = Int((100 - 1 + 1) * Rnd + 1)
Cells(i, 1) = arr(i, 1)
Next i
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
Stop
''' find the top 20
For i = 1 To 999
For j = i + 1 To 1000
If arr(i, 1) = arr(j, 1) Then
arr(i, 2) = arr(i, 2) + 1
arr(j, 1) = ""
End If
Next j
Next i
Stop

j = 1
For i = 1 To 1000
If arr(i, 1) <> "" Then
Cells(j, 2) = arr(i, 1): Cells(j, 3) = arr(i, 2)
j = j + 1
End If
Next i
End Sub



Hello,

Your second question is not clear to me, what do you mean with the top 20
numbers? 20 most frequently generated numbers?

Thanks for your reply.

When a 1,000 random numbers are displayed, using a 1,000 rows,
take the frequency of the 1000 numbers and display on the top 20
most frequent numbers,different numbers. I suspect some numbers
would be repeated several times. Thereby, using only 20 rows not 1,000
rows

With thanks

 

[smandula]

Hello,
Thanks for your reply. You are right, in the top 20 there will be a
lot of repeated
numbers. That is why a count is necessary of similar numbers to find
which
numbers are most frequent as a group.

I am hoping to find a solution in gather all similar numbers, grouping
them, and
then find the 20 highest or most frequent numbers.

Being random these numbers change all the time.

With thanks

 

[Bernd P]

Hello,

If you copy
=INT(RAND()*100+1)
into cells A1:A1000 and then select cells B1:C20 and array-enter
=Gsort(Pfreq(A1:A1000),"DD","NN","21")
you will get the 20 most frequent numbers (numbers and their frequency
sorted descending)

With
=Gsort(Pfreq(A1:A1000),"DD","NN","12")
array-entered you will get the 20 highest numbers and their frequency.

GSort and Pfreq are UDF's which you can find here:
http://sulprobil.com/html/pfreq.html
http://sulprobil.com/html/sort_vba.html

If are generally interested in random number generation:
http://sulprobil.com/html/random_numbers.html

Regards,
Bernd

 

[JP Ronse]

Hi,

Hereafter the code to generate 1000 random numbers and take the 20 with
highest occurence.

I have to thank Rick Rothstein and Chip Pearson for their feedback on
collections and their advise, but a special thank to Dana De Louis who
provided me some sample code for the use of dictionaries. As you can see,
I've made use of it.

The bubble sort routine was found on following page:

http://www.schouppe.net/comlog/tabellen/sorteren/bubbleSort.htm

(this page is in Dutch, the sample code also, my only contribution was to
translate the code in English and changing the sorting order from ascending
to descending).

Trying to answer your question was also a very instructive experiment for
myself.

Wkr,

JP

Sub Random2()
Dim dicRnd As Object
Dim intRnd As Integer
Dim i As Integer
Dim varKeys As Variant
Dim varValues As Variant
Dim arr()
Dim intTmp As Integer
Dim blnSorted As Boolean
Dim intUnsorted As Integer

Set dicRnd = CreateObject("Scripting.Dictionary")

For i = 1 To 1000
intRnd = Int((100 - 1 + 1) * Rnd + 1)

If dicRnd.Exists(intRnd) Then
dicRnd(intRnd) = dicRnd(intRnd) + 1
Else
dicRnd.Add Key:=intRnd, Item:=1
End If
Next i

varKeys = dicRnd.Keys
varValues = dicRnd.Items

ReDim arr(0 To UBound(varKeys), 1 To 2)

For i = 0 To UBound(varKeys)
arr(i, 1) = varKeys(i): arr(i, 2) = varValues(i)
Next i

blnSorted = False
intUnsorted = UBound(varKeys)

Do While (blnSorted = False And intUnsorted > 0)
blnSorted = True
intUnsorted = intUnsorted - 1
i = 0

Do While i <= intUnsorted
If arr(i, 2) < arr(i + 1, 2) Then
intTmp = arr(i, 1)
arr(i, 1) = arr(i + 1, 1)
arr(i + 1, 1) = intTmp

intTmp = arr(i, 2)
arr(i, 2) = arr(i + 1, 2)
arr(i + 1, 2) = intTmp

blnSorted = False
End If
i = i + 1
Loop
Loop

''' uncomment this for testing
'''For i = 1 To 99
''' Cells(i, 1) = varKeys(i - 1): Cells(i, 2) = varValues(i - 1):
Cells(i, 3) = arr(i - 1, 1): Cells(i, 4) = arr(i - 1, 2)
'''Next i
''' end uncomment

''' comment this for testing
For i = 0 To 19
Cells(i + 1, 1) = arr(i, 1)
Next i
''' end comment
End Sub


Hello,

Your second question is not clear to me, what do you mean with the top 20
numbers? 20 most frequently generated numbers?

Thanks for your reply.

When a 1,000 random numbers are displayed, using a 1,000 rows,
take the frequency of the 1000 numbers and display on the top 20
most frequent numbers,different numbers. I suspect some numbers
would be repeated several times. Thereby, using only 20 rows not 1,000
rows

With thanks

 

[Dana DeLouis]

Hi. There are lots of different approaches. Here is just another.
Note that 'Large doesn't work well when there are duplicates.

Sub Demo()
Dim v(1 To 100)
Dim n, j, p

' 1000 random numbers between 1 & 100
For j = 1 To 1000
n = RandomInteger(1, 100)
v(n) = v(n) + 1
Next j

' Top 20
With WorksheetFunction
For j = 1 To 20
n = .Large(v, j)
p = Position(n, v)
Debug.Print "#" & p, n
v(p) = -1 'No longer valid
Next j
End With
End Sub

Function Position(n, m)
'// Position of 'n within array 'm

Position = WorksheetFunction.Match(n, m, 0)
End Function

Function RandomInteger(L, H)
RandomInteger = Int((H - L + 1) * Rnd + L)
End Function

= = = = = =
HTH :>)
Dana DeLouis

 

[smandula]

Thanks folks,

I would like to Thank everyone for their input.