Home |
Search |
Today's Posts |
#1
|
|||
|
|||
RANDBETWEEN query
Hi everyone, hope someone can help. I have a list of 16 names, numbered 1 to 16 in the range: A1 to B17 where column A has the names, and column B the sequence from 1 to 16. I have a cell with the formula: RANDBETWEEN(1,16) which gives you a number between 1 and 16 each time you press F9 to recalculate. However, I would like to change the formulae so that if you recalculate and get the number 6 (for example), you won't get 6 again until the other 15 numbers have also been shown. Is this possible using a function or VBA ? Many thanks for your help! -- scottwilsonx ------------------------------------------------------------------------ scottwilsonx's Profile: http://www.excelforum.com/member.php...o&userid=11128 View this thread: http://www.excelforum.com/showthread...hreadid=274544 |
#2
|
|||
|
|||
Hi
see: http://www.mcgimpsey.com/excel/udfs/randint.html -- Regards Frank Kabel Frankfurt, Germany "scottwilsonx" schrieb im Newsbeitrag ... Hi everyone, hope someone can help. I have a list of 16 names, numbered 1 to 16 in the range: A1 to B17 where column A has the names, and column B the sequence from 1 to 16. I have a cell with the formula: RANDBETWEEN(1,16) which gives you a number between 1 and 16 each time you press F9 to recalculate. However, I would like to change the formulae so that if you recalculate and get the number 6 (for example), you won't get 6 again until the other 15 numbers have also been shown. Is this possible using a function or VBA ? Many thanks for your help! -- scottwilsonx --------------------------------------------------------------------- --- scottwilsonx's Profile: http://www.excelforum.com/member.php...o&userid=11128 View this thread: http://www.excelforum.com/showthread...hreadid=274544 |
#3
|
|||
|
|||
Frank Kabel wrote... see: http://www.mcgimpsey.com/excel/udfs/randint.html ... Incomplete answer. Randint could provide a shuffled array of numbers from 1 to 16, but to sample from it cyclically would require another UDF like Function cycrnd() As Long Static a As Variant, n As Long Application.Volatile 'necessary for this If IsEmpty(a) Then a = randint(1, 16) n = LBound(a) Else If n < UBound(a) Then n = n + 1 Else n = LBound(a) End If cycrnd = a(n) End Function -- hgrove ------------------------------------------------------------------------ hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=274544 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query of External Data | Excel Discussion (Misc queries) | |||
saving wkbk minus MS Query links | Excel Discussion (Misc queries) | |||
Pivot Tables referring to external data query | Excel Discussion (Misc queries) | |||
New web query with Excel Pro Edition 2003 | Links and Linking in Excel | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |