Help with Rand

[Jack]

I am trying to randomly generate a series of numbers based on another series
of numbers. If i have the numbers 1 to 15 sequentially in A1 to A15, then how
do i randomly generate one of these numbers in C1. Currently i am trying -
=RAND()*A1:A15, but this is not working. The numbers in A1:A15 may well
change and this is why i don't want to simply use =RAND()*15

 

[joeu2004]

If i have the numbers 1 to 15 sequentially in
A1 to A15, then how do i randomly generate
one of these numbers in C1.

=OFFSET(A1,RAND()*COUNT(A1:A15),0)

 

[Jack]

Thanks very much for the prompt reply.

Could you maybe explain what you have done so that it will assist me in
modifying this and future use of this? I am not sure as to why you need to
reference A1 and what the Count does?

 

[joeu2004]

Could you maybe explain what you have done so that
it will assist me in modifying this and future use of this?
I am not sure as to why you need to reference A1 and
what the Count does?

Use Help > Excel Help to learn about functions and their
parameters. After your read the Help text, if you still
have questions, fire away!

I used COUNT() to generalize. You could replace COUNT()
with 15.

 

[Harlan Grove]

(e-mail address removed) wrote...

=OFFSET(A1,RAND()*COUNT(A1:A15),0)

COUNT is only useful when there might be some nonnumeric cells, but
your formula would assume any nonnumeric cells were in the bottom rows
of the range. If there might be nonnumeric cells in the range, better
to use

=LARGE(A1:A15,INT(1+COUNT(A1:A15)*RAND()))

or

=SMALL(A1:A15,INT(1+COUNT(A1:A15)*RAND()))

If the range would always have all numeric cells, better either to
hardcode the cell count or use ROWS rather than COUNT. Note that the
LARGE and SMALL formulas above have no difficulty returning values from
arbitrary ranges or even 3D references.