inserting randomly generated numbers into a table

[Jim S]

i am trying to create 50 tables of 25cells and generate
a number between 1 and 25 in each cell. each table has to
have differnt numbers in the corresponding cells.
i have tried randbetween but that duplicates numbers in
the same table. can anyone help???

 

[Frank Kabel]

Hi Jim
have a look at
http://www.mvps.org/dmcritchie/excel/random.htm

Frank

 

[Harlan Grove]

i am trying to create 50 tables of 25cells and generate
a number between 1 and 25 in each cell. each table has to
have differnt numbers in the corresponding cells.
i have tried randbetween but that duplicates numbers in
the same table. can anyone help???

There's a way to do this using only formulas and one cell per result, so 25
cells give 25 distinct random integers, but it requires that the cells span
either a single column or a single row. I'll assume a single column, e.g.,
A1:A25. Enter these formulas.

A1:
=INT(1+25*RAND())

A2: [array formula]
=SMALL(IF(COUNTIF(A$1:A1,ROW(INDIRECT("1:25")))=0,
ROW(INDIRECT("1:25"))),INT(1+(25-COUNT(A$1:A1))*RAND()))

Select A2 and fill down into A3:A25. This will give integers 1 to 25 in random
order in A1:A25.

 

[Harlan Grove]

have a look at
http://www.mvps.org/dmcritchie/excel/random.htm

You recommend MOREFUNC.XLL fairly often. This would also be a good time to do
so. It's MRAND function, specifically MRAND(25,1,25) or MRAND(25,1,25,1), would
handle this more efficiently than the VBA code at David McRitchie's site.

 

[Frank Kabel]

...

You recommend MOREFUNC.XLL fairly often. This would also be a good
time to do so. It's MRAND function, specifically MRAND(25,1,25) or
MRAND(25,1,25,1), would handle this more efficiently than the VBA
code at David McRitchie's site.

Thanks Harlan, good point
Frank