michaelsmith559
Well-known Member
- Joined
- Oct 6, 2013
- Messages
- 881
- Office Version
- 2013
- 2007
I want to know if it is possible to have excel input a list of random numbers (I will show macro I am currently using and results) based on a starting seed (I have read about using rnd -1 before randomize but am not sure how to make that work for repeating a sequence. If I can make excel repeat the sequence and change the sequence by changing rnd -1 to rnd -2, then I would like to use solver to find the best seed for a "predictable" spacing. Here is my sheet the first 20 rows:
Here is the macro I used to generate the random values and to count the number of trials it took to reach the value in column Q.
Column T has the desired spacing I would like to have between the trials in column S. Would it be possible to somehow use a random seed, and have solver change the seed so that the number of trials in column s has a more linear/predictable spacing (again I have read about using rnd -1 to repeat a pattern which could be useful if solver could change the seed and achieve what I want)?
Excel 2007 | |||||||
---|---|---|---|---|---|---|---|
Q | R | S | T | U | |||
1 | Value | Random | Trials | Desired Spacing | Seed | ||
2 | 1 | 1 | 6 | 2 | -1 | ||
3 | 3 | 3 | 24 | 4 | |||
4 | 3 | 3 | 26 | 6 | |||
5 | 4 | 4 | 48 | 8 | |||
6 | 5 | 5 | 65 | 10 | |||
7 | 1 | 1 | 74 | 12 | |||
8 | 1 | 1 | 78 | 14 | |||
9 | 4 | 4 | 80 | 16 | |||
10 | 3 | 3 | 86 | 18 | |||
11 | 8 | 8 | 104 | 20 | |||
12 | 8 | 8 | 107 | 22 | |||
13 | 9 | 9 | 125 | 24 | |||
14 | 9 | 9 | 136 | 26 | |||
15 | 2 | 2 | 155 | 28 | |||
16 | 3 | 3 | 159 | 30 | |||
17 | 5 | 5 | 159 | 32 | |||
18 | 7 | 7 | 180 | 34 | |||
19 | 1 | 1 | 217 | 36 | |||
20 | 9 | 9 | 233 | 38 | |||
One |
Here is the macro I used to generate the random values and to count the number of trials it took to reach the value in column Q.
Code:
Sub Macro1()
Dim LRandomNumber As Integer, icount As Long, i As Long, lr As Long
lr = Cells(Rows.Count, "Q").End(xlUp).Row
icount = 0
For i = 2 To lr
LRandomNumber = Int((9 - 1 + 1) * Rnd + 1)
Cells(i, "R").Value = LRandomNumber
While Cells(i, "R").Value <> Cells(i, "Q").Value
LRandomNumber = Int((9 - 1 + 1) * Rnd + 1)
Cells(i, "R").Value = LRandomNumber
icount = icount + 1
Wend
Cells(i, "S").Value = icount
Next i
End Sub
Column T has the desired spacing I would like to have between the trials in column S. Would it be possible to somehow use a random seed, and have solver change the seed so that the number of trials in column s has a more linear/predictable spacing (again I have read about using rnd -1 to repeat a pattern which could be useful if solver could change the seed and achieve what I want)?