Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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/Mod...werTools.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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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 "smandula" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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 he 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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/tabel...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 "smandula" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
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 JP Ronse wrote: 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/tabel...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 "smandula" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
Thanks folks,
I would like to Thank everyone for their input. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Numbers excluding Previous Numbers | Excel Worksheet Functions | |||
getting numbers divisible by 4 from random numbers in ascending order | Excel Worksheet Functions | |||
Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |