inserting randomly generated numbers into a table

  • Thread starter Thread starter Jim S
  • Start date Start date
J

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???
 
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???
Click to expand...

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 said:
...

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.
Click to expand...

Thanks Harlan, good point
Frank
 
Back
Top