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?

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?

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

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?

"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
---

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
---

"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
---