generate random numbers

[Guest]

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?

 

[Max]

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

 

[sulprobil]

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

 

[Guest]

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]

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

 

[Guest]

That's terrific, Max!! It does exactly what I intended it to do now.

 

[Max]

That's terrific, Max!! It does exactly what I intended it to do now.

Pleased to hear that !
Thanks for the feedback