Random Numbers excluding Previous Numbers

[Brad]

Hi

We have a system in New Zealand where we need to randomly select one day a
week over one year, simple in itself. However we are to incude all the days
of the week over a 5 week period e.g if we choose Tuesday this week the other
four days need to be randomly selected the following week, three days need
the following week etc restarting five days again at week six.

I need a formular or method of excluding previously selected numbers but
giving all remaining numbers an equal chance of selection.

This needs to be set out on a spread sheet for the entire year

Would really appreciate some advice

Thanks

Regards Brad

 

[Bill Kuunders]

one way

a1 to a5 have the same formula............... =rand()

b1 to b5 have the single days as text ....... Mon Tue Wed Thu Fri

c1 has the formula =VLOOKUP(LARGE(A$1:A$5,1),A$1:B$5,2,FALSE)
d1 has the formula =VLOOKUP(LARGE(A$1:A$5,2),A$1:B$5,2,FALSE)
e1 has the formula =VLOOKUP(LARGE(A$1:A$5,3),A$1:B$5,2,FALSE)
f1 has the formula =VLOOKUP(LARGE(A$1:A$5,4),A$1:B$5,2,FALSE)
g1 has the formula =VLOOKUP(LARGE(A$1:A$5,5),A$1:B$5,2,FALSE)

note............. only one number changes from 1 to 5


now each time you press "F9" the rand formula in A1 to A5 will recalculate
and the vlookup formula
will give you a new series of days at random.
all you need to do now is <copy> and <paste special> <values>about 11 times


Bill K
Waiau Pa

 

[Jim Cone]

Re: "...I need a formula or method..."

A VBA method follows.
Paste the following code in a standard module.
Add a button to a worksheet and assign the macro "Every5Weeks" to it.

Enter week numbers from 1 to 52 in a column.
Select the cell next to the current week number (or any week number) and click the button.

'code starts....
Sub Every5Weeks()
'Jim Cone - Portland, Oregon USA - July 2009
'Adds a random day of the week to the active cell.
Dim rng As Excel.Range
Dim newRng As Excel.Range
Dim vDays As Variant
Dim strDay As String
Dim lngIndex As Long
Dim DaysOffset As Long

Set rng = ActiveCell
If rng.Column < 2 Then
MsgBox "Use any column except column A. ", _
vbExclamation, "Every Five Weeks"
Exit Sub
ElseIf Application.CountA(rng.Resize(51, 1).Offset(1, 0)) > 0 Then
MsgBox "Clear the cells below " & rng.Address(False, False) & " ", _
vbExclamation, "Every Five Weeks"
Exit Sub
ElseIf Not IsNumeric(rng(1, 0).Value) Or LenB(rng(1, 0).Value) = 0 Then
rng.Value = "Week numbers required in column " & rng.Column - 1
Exit Sub
ElseIf rng(1, 0).Value > 52 Or rng(1, 0).Value < 1 Then
rng.Value = "Invalid week number in column"
Exit Sub
End If


vDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
DaysOffset = rng.Offset(0, -1).Value Mod 5
If DaysOffset <> 0 Then
Set newRng = rng.Offset(-DaysOffset, 0).Resize(DaysOffset, 1)
Else
lngIndex = Int((4 - 0 + 1) * Rnd + 0)
strDay = vDays(lngIndex)
rng.Value = strDay
rng.Font.Bold = True
rng(2, 1).Select
Exit Sub
End If

Do
' Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
lngIndex = Int((4 - 0 + 1) * Rnd + 0)
strDay = vDays(lngIndex)
Loop Until IsError(Application.Match(strDay, newRng, 0))

rng.Value = strDay
rng.Font.Bold = False
rng(2, 1).Select
End Sub
'....code ends

'--
Jim Cone
Portland, Oregon USA




"Brad" <[email protected]>
wrote in message
Hi
We have a system in New Zealand where we need to randomly select one day a
week over one year, simple in itself. However we are to incude all the days
of the week over a 5 week period e.g if we choose Tuesday this week the other
four days need to be randomly selected the following week, three days need
the following week etc restarting five days again at week six.
I need a formular or method of excluding previously selected numbers but
giving all remaining numbers an equal chance of selection.
This needs to be set out on a spread sheet for the entire year
Would really appreciate some advice
Thanks
Regards Brad