 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2007Random numbers
How to generate matrix of nonrepeated random numbers in Exel? manipulating the numbers by eliminating duplicates in effect means they are not random anymore. However on way:- Suppose you want 100 random numbers, fill column A1 to (say)A1000 with the numbers 1 to 1000 and in B1 enter =Rand() and copy down. Sort the 2 columns on Column B and select the top 100 numbers in col A as your 100 different random numbers and copy them into your matrix Mike > How to generate matrix of nonrepeated random numbers in Exel? Thanks, but it doesn’t work for me. To be more clear, I’ll give you an example: I’m using =ROUND(RAND()*(999-0)+0;0) to generate three digits random numbers from 0 to 999. I copyed this formula in a cells A1:E20 (100 cells). In some generations I’m receiving duplicated numbers in two or more cells, which I don’t want. This is the problem, wtich I want to solve. Regards > manipulating the numbers by eliminating duplicates in effect means they are > not random anymore. However on way:- [quoted text clipped - 7 lines] > > > How to generate matrix of nonrepeated random numbers in Exel? It works for me. Populate a column (Say H) with the numbers 1 to 999 (your range) and in an adjacent column (I) type =rand() and drag down. In A1 of your matrix type =H1 and drag down to A20 In B1 of your matrix type =H21 and drag down to B20 Repeat this for all the columns in your array to produce your 5 * 20 matrix. Hit F9 to make the sheet recalcutate. Select columns H & I and sort by column I You now have a matrix of non-repeating random numbers in the tange 1 - 999. Mike > Thanks, but it doesn’t work for me. To be more clear, I’ll give you an example: > I’m using =ROUND(RAND()*(999-0)+0;0) to generate three digits random numbers [quoted text clipped - 14 lines] > > > > > How to generate matrix of nonrepeated random numbers in Exel? Thsi solution will work. It requires a custtom function Put in Cell E8 and copy down the row =SingleRnd(E$7:E7) Make sure E7 is an empty cell the random function checks cells in range to make sure no duplicates are generated Here is the function Function SingleRnd(Numbers As Range) Do While (1) SingleRnd = Rnd Found = False For Each MyNumber In Numbers If (MyNumber = SingleRnd) Then Found = True Exit For End If Next MyNumber If Found = False Then Exit Do Loop End Function > Thanks, but it doesn’t work for me. To be more clear, I’ll give you an example: > I’m using =ROUND(RAND()*(999-0)+0;0) to generate three digits random numbers [quoted text clipped - 14 lines] > > > > > How to generate matrix of nonrepeated random numbers in Exel? Hello, Take my function UniqRandInt from www.sulprobil.com, I suggest. Regards, Bernd |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.