| 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 | ||
| 213962 | 2004-02-09 07:56:00 | I need to do a grid of numbers for maths exercise for my son. Ten lines, ten rows. Each entry is randomly selected number from 1-10, but there shalt be no repeats. How can I do this? I put this into the second column, to check if it repeats the first column. The chance of a repeat is not actually reduced, as the formula requires the recalced result regardles of the result so actually a complete bollocks. =IF(INT(RAND()*10+1)=B1,INT(RAND()*10+1),INT(RAND( )*10+1)) I need to pick from a list somehow, until the numbers are "used up". Any ideas? robo. PS Have a killer maths spreadsheet for exercising children on all sorts if anyone wants it. It's nasty, been developed over the years, includes random calcs for additional, subtraction, multiplication, division, negative numbers, decimals. I did say nasty. |
robo (205) | ||
| 213963 | 2004-02-09 08:00:00 | I dont know any thing about waht can be done in XL from a programing angle I would start with a list on the numbers 1 to 10 in cells then pick two cells at random and switch the contence of those cells. repeat as many random swaps as you like. |
robsonde (120) | ||
| 213964 | 2004-02-09 08:10:00 | There was a thread on related matters in this Forum on and about 27 jan 2003. Maybe you might find something in this. If not I might have look at it for you, although it seems even such arcane stuff can sometimes generate not-very-well-informed dissent (not coming from you of course). |
rugila (214) | ||
| 213965 | 2004-02-09 08:14:00 | Somehow randomly swapping or picking pairs might work. Don't want to resort to macros. I could have a table of combinations that include every number in the range and randomly select a line in the table for a vlookup but then I have to do the sodding table and that is what I wanted to avoid. Any other ideas? robo. |
robo (205) | ||
| 213966 | 2004-02-09 08:44:00 | What do you mean - "no repeats". Do you mean the cells in each row (or column) will contain each integer from 1 to 10 once and only once? Or do you mean that there may not be rows (or columns) with an identical sequence? Or both? Whatever your reply, I'm not sure if I can help, but it could be an interesting exercise... Tony Bacon |
tbacon_nz (865) | ||
| 213967 | 2004-02-09 08:49:00 | My suggestion would be to resort to making your own custom function which would give a much greater control over the set of numbers. Yould would need to have 9 input variables so that you can test that your random number has not already used and use a do loop to crate random number each time. | me_ill (5233) | ||
| 213968 | 2004-02-09 09:36:00 | I don't know how you translate it into "Excelese", but something like this ought to work: dim Array1(10) ' 10 flags – on if the number is selected, off if not For rows 1 to 10 Set all array1 off For cells 1 to 10 Do until array1 all on Pick a random number (n) between 1 and 10 If array1(n) on then do until array(n) off Pick another random n Loop Endif Put n into current cell Loop Next cell Next row Or maybe not! Tony Bacon |
tbacon_nz (865) | ||
| 213969 | 2004-02-09 09:37:00 | All my lovely indenting disappeared! And I think I just said the same thing as me_ill. Tony B |
tbacon_nz (865) | ||
| 213970 | 2004-02-09 09:53:00 | Hi, copy both lots of code into a module then just select 10 vertical cells then run the CreateUniqueRandomNumbers macro then repeat the process for each column. Code harpooned from here (www.exceltip.com) with a small adjustment. :-) Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant ' creates an array with NumCount unique long random numbers in the range LLimit - ULimit (including) Dim RandColl As Collection, i As Long, varTemp() As Long UniqueRandomNumbers = False If NumCount < 1 Then Exit Function If LLimit > ULimit Then Exit Function If NumCount > (ULimit - LLimit + 1) Then Exit Function Set RandColl = New Collection Randomize Do On Error Resume Next i = CLng(Rnd * (ULimit - LLimit) + LLimit) RandColl.Add i, CStr(i) On Error GoTo 0 Loop Until RandColl.Count = NumCount ReDim varTemp(1 To NumCount) For i = 1 To NumCount varTemp(i) = RandColl(i) Next i Set RandColl = Nothing UniqueRandomNumbers = varTemp Erase varTemp End Function Sub CreateUniqueRandomNumbers() Dim varrRandomNumberList As Variant varrRandomNumberList = UniqueRandomNumbers(10, 1, 10) Selection.Value = Application.Transpose(varrRandomNumberList) End Sub |
parry (27) | ||
| 213971 | 2004-02-09 10:06:00 | Had a bit of a think about this and just want to be clear about what you want. As far as I can tell you want to randomly reorder the numbers 1 to 10 without repetition. Suppose that you put the numbers 1 to 10 in cells B1 to B10. In A1 put =rand() and fill down to A10. Then select the range A1 to B10 and sort on A1 using your sort up or sort down button. The numbers 1 to 10 in col B will be randomly reordered, and you can rep[eat this as many times as you like. This doesn't involve any code. Is this the sort of thing you want? |
rugila (214) | ||
| 1 2 | |||||