Help with Rand

[Guest]

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

 

[Guest]

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)

 

[Guest]

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?

 

[Dirk Van de moortel]

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?

Count() counts the number of non-empty cells in the
specified range.
If not all the cells in the range are filled, the function
would fail because you would never get one of the
values starting on and below the cell on the row
corresponding to the number of non-empty cells.

Since you have specified that all the cells between
A1 and A15 are filled, the result will always be 15,
so you don't need to do the counting and better use

= OFFSET( A1, RAND()*15, 0 )

which gives you the value of the cell which is
rand()*15 rows below and 0 columns to the right
of the reference cell A1.

If you expect empty cells, and don't want to count
and hardcode the number of rows in your range,
then you can use the function ROWS(A1:A15):

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

When it picks an empty cell, it will generate a zero.
This works with a mix of empty and non-empty cells.

Dirk Vdm

 

[Guest]

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.