Home |
Search |
Today's Posts |
#1
|
|||
|
|||
generate random numbers
How do I generate random numbers within a range of cells and not equal to
each other? If I have 5 cells, and I want 5 random numbers between 1 and 5, but with each cell having a different number than any of the other cells, how do I do this? |
#2
|
|||
|
|||
One play to try ..
Assume the list to be randomized is in A1:A5 A1:A5 can contain any type of list, e.g.: the numbers 1 - 5, Text1, Text2 ... Text5, etc Put in say, E1: =RAND() Copy down to E5 Put in B1: =INDEX($A$1:$A$5,MATCH(SMALL(E:E,ROW(A1)),E:E,0)) Copy down to B5 B1:B5 will generate a randomized, non-repeating scramble of whatever's in A1:A5, with each recalc Just tap / press F9 to regenerate afresh -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "Jules" wrote in message ... How do I generate random numbers within a range of cells and not equal to each other? If I have 5 cells, and I want 5 random numbers between 1 and 5, but with each cell having a different number than any of the other cells, how do I do this? |
#3
|
|||
|
|||
Take a macro:
http://www.sulprobil.com/html/uniqrandint.html Select your five cells, enter =UniqRandInt(5) and finish with CTRL+SHIFT+ENTER (enter as array formula) HTH, sulprobil |
#4
|
|||
|
|||
Thanks, this was very helpful. Is there a way to make sure that the same
number is not generated on the same row? For example, A3 doesn't generate to a 3 in B3? "Max" wrote: One play to try .. Assume the list to be randomized is in A1:A5 A1:A5 can contain any type of list, e.g.: the numbers 1 - 5, Text1, Text2 ... Text5, etc Put in say, E1: =RAND() Copy down to E5 Put in B1: =INDEX($A$1:$A$5,MATCH(SMALL(E:E,ROW(A1)),E:E,0)) Copy down to B5 B1:B5 will generate a randomized, non-repeating scramble of whatever's in A1:A5, with each recalc Just tap / press F9 to regenerate afresh -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "Jules" wrote in message ... How do I generate random numbers within a range of cells and not equal to each other? If I have 5 cells, and I want 5 random numbers between 1 and 5, but with each cell having a different number than any of the other cells, how do I do this? |
#5
|
|||
|
|||
"Jules" wrote
Thanks, this was very helpful. You're welcome ! ... Is there a way to make sure that the same number is not generated on the same row? For example, A3 doesn't generate to a 3 in B3? Not really, since it's random <g, but if you want to monitor the randomization happening within B1:B5 to "satisfy" the condition: "the same number is not generated on the same row" you could always put in say, B6: =IF(OR(A1=B1,A2=B2,A3=B3,A4=B4,A5=B5),"No, press F9 again","Yes, you got it!") Then just keep tapping F9 until you see the phrase: "Yes, you got it!" in B6 and do a copy paste special values elsewhere to freeze the results in B1:B5 -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- |
#6
|
|||
|
|||
That's terrific, Max!! It does exactly what I intended it to do now.
"Max" wrote: "Jules" wrote Thanks, this was very helpful. You're welcome ! ... Is there a way to make sure that the same number is not generated on the same row? For example, A3 doesn't generate to a 3 in B3? Not really, since it's random <g, but if you want to monitor the randomization happening within B1:B5 to "satisfy" the condition: "the same number is not generated on the same row" you could always put in say, B6: =IF(OR(A1=B1,A2=B2,A3=B3,A4=B4,A5=B5),"No, press F9 again","Yes, you got it!") Then just keep tapping F9 until you see the phrase: "Yes, you got it!" in B6 and do a copy paste special values elsewhere to freeze the results in B1:B5 -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- |
#7
|
|||
|
|||
"Jules" wrote
That's terrific, Max!! It does exactly what I intended it to do now. Pleased to hear that ! Thanks for the feedback -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
How to sort random numbers in columns | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
Paste rows of numbers from Word into single Excel cell | Excel Discussion (Misc queries) |