| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 42371 | 2004-02-09 07:56:00 | Nasty Excel worksheet - need ten numbers in a random order | robo (205) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 213972 | 2004-02-09 18:58:00 | Okay Thanks for suggestions, but have it sussed. Not perfect but it will do. Do a list of numbers 1-10. Then put a random number formula in the left columun (simple one =rand() and that's it). Then sort the table on the random numbers, that orders the ten digits randomly. I then dumped another set of ten in 1-10 order alongside the random set, and repeated to have two different random orders, and repeated again, etc. Now have a table of 20-odd randomly selected sets, giving 400 possible combinations on the tables so repeats are unlikely to be detected. The formula in the headings now reads: =VLOOKUP(8,$N$1:$AI$10,$M$1) in column 8, and: =VLOOKUP(1,$N$1:$AI$10,$M$3) in row 1. (M1 and M3 are where the random numbers are, all entries across must use the same random number, and all down must use the same, different, random number. Doing the table of numbers - ie answering them by multiplying - is damn hard. I can do 60 random ones in 60 seconds when I am motoring but the grid of 100 took me over 3 minutes. Again, thanks for efforts. robo. |
robo (205) | ||
| 213973 | 2004-02-09 19:50:00 | I still don't quite follow, but you can use =RANK(a1,$a1:$a10) copied from B1:B10 on a list of 10 random numbers in A1:A10 to generate the numbers 1 to 10 in the same random order as A1:A10 |
Russell D (18) | ||
| 213974 | 2004-02-09 20:00:00 | Just curious wouldn't just starting each column with one number down have the same effect- 12345678910 then 23456789101 etc...Each cell has a different number and no number is repeated. | mark.p (383) | ||
| 213975 | 2004-02-09 21:56:00 | I thought about that - but I guess it depends on how truly random you want the numbers to be. If random means you can'r predict the next number by inspecting the previous sequence, then your solution is not random. But maybe for the purposes of the exercise, it would be sufficient. Tony Bacon |
tbacon_nz (865) | ||
| 213976 | 2004-02-09 23:23:00 | Have you had a look at that excellent little book by Rob Clarke? ;\ :D |
Chris Randal (521) | ||
| 213977 | 2004-02-10 03:51:00 | I refer to page 17 all the time (it does say you don't have to remember, so I don't). Am going to look at RANK function. robo. |
robo (205) | ||
| 1 2 | |||||