Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jules
 
Posts: n/a
Default 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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
sulprobil
 
Posts: n/a
Default

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   Report Post  
Jules
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

"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   Report Post  
Jules
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
How to sort random numbers in columns webehere Excel Discussion (Misc queries) 3 January 15th 05 01:24 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
Sorting imported "numbers" Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 08:33 PM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM


All times are GMT +1. The time now is 10:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"