Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Numbers excluding Previous Numbers
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Numbers excluding Previous Numbers
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 <valuesabout 11 times :):) Bill K Waiau Pa "Brad" 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Numbers excluding Previous Numbers
"...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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting numbers divisible by 4 from random numbers in ascending order | Excel Worksheet Functions | |||
How to select top six numbers from a of range of random numbers | Excel Worksheet Functions | |||
I want random numbers generated without repeating numbers | Excel Worksheet Functions | |||
Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions |