Random Number - Statistical Sample Question
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Random Number - Statistical Sample Question

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello again,

    Another day, another question.....(i'm editing this post with a more concise explanation of what I intend to do..3/25):

    I run a periodic "random sample" report for staff here at work. The staff has a set of providers they monitor. They require a random sampling of those provider's clients (usually between 10 and 150 clients per provider). Each client has a ID# assigned to them...from those I generate the sample.

    In Column "A" I have a listing of unique client numbers (the number of client numbers will vary every time). The list always starts in cell "A17"..but that is pretty much the only constant here. The number of numbers that start at A17 can be anywhere from 10 to 150..if not more (ie, go up to cell A167 or more).

    What I want to do is have a macro (or function?) dynamically select all 'filled' cells from A17 until the end of that list.. then randomly select 25% of those client numbers (ie, if there are 100 client numbers, it will randomly select 25 unique client numbers from that list), and then have the macro place those numbers into a seperate area of the worksheet (starting at cell C17).

    I hope I'm not too long-winded, but any help here would be greatly appreciated! If anyone else has any suggestions here, please don't hesitate to respond.

    Thanks in advance,

    Rob

    [ This Message was edited by: Mopacs on 2002-03-25 11:06 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-20 14:17, Mark W. wrote:
    Take a look at my response at...

    http://www.mrexcel.com/board/viewtop...c=2254&forum=2
    Wonderful.. that posting is very relevant to my question. I will try and apply your suggestions to my project in the morning. If anyone has additional suggestions, those are welcome too!

    Thanks again,

    Rob

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    This code picks one random name from a list of names. It can be modified to do your %'s.

    Sub myRnd()
    'Find a random name in a existing names list.
    'By Joe Was, 6/27/2001.

    Dim myOrder As Range
    Dim myName
    Dim mySelect As Variant
    Randomize
    'Note: The 20 below = the ending ROW of your names list.
    ' The 1 below = the starting ROW of your names list.
    mySelect = Int((20 * Rnd) + 1)

    'Note: The "A" below is the column where your names list is.
    myName = Range("A" & mySelect)

    'Put the answer in a cell
    Worksheets("Sheet1").Range("C1") = myName

    'Put the answer in a screen message box.
    MsgBox "The selection is:" & Chr(13) & Chr(13) & myName
    End Sub

    You can comment out the MsgBox code, its an option! Hope this gets you started. JSW

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-20 14:31, Joe Was wrote:
    This code picks one random name from a list of names. It can be modified to do your %'s.

    Sub myRnd()
    'Find a random name in a existing names list.
    'By Joe Was, 6/27/2001.

    Dim myOrder As Range
    Dim myName
    Dim mySelect As Variant
    Randomize
    'Note: The 20 below = the ending ROW of your names list.
    ' The 1 below = the starting ROW of your names list.
    mySelect = Int((20 * Rnd) + 1)

    'Note: The "A" below is the column where your names list is.
    myName = Range("A" & mySelect)

    'Put the answer in a cell
    Worksheets("Sheet1").Range("C1") = myName

    'Put the answer in a screen message box.
    MsgBox "The selection is:" & Chr(13) & Chr(13) & myName
    End Sub

    You can comment out the MsgBox code, its an option! Hope this gets you started. JSW
    Hey Joe,

    Thanks for responding. Well I'm having a little trouble implementing your code. Let me see if I understand correctly. You are pointing to a list of numbers in a particular column (A?) and then specifying the set number of rows it will select from in column A? Then it selects one number at random from that list, and places that number in a specific cell?

    You'll have to excuse my confusion! I believe I understand what you are trying to do..though I may be WAY off!

    Anyways, to give you (or anyone else who reads this) a more specific example of what I am doing, it is this:

    In Column "A" I have a listing of unique client numbers (the number of client numbers will vary every time). The list always starts in cell "A17"..but that is pretty much the only constant here. The number of numbers that start at A17 can be anywhere from 10 to 150..if not more (ie, go up to cell A167 or more).

    What I want to do is have the macro (or function?) dynamically select all 'filled' cells from A17 until the end of that list.. then randomly select 25% of those client numbers (ie, if there are 100 client numbers, it will randomly select 25 unique client numbers from that list), and then have the macro place those numbers into a seperate area of the worksheet (starting at cell C17).

    I hope I'm not too long-winded, but any help here would be greatly appreciated! If anyone else has any suggestions here, please don't hesitate to respond either.

    Thank You,

    Rob

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could possibly Adapt an answer I gave for a Random League Generator:

    check:

    http://mrexcel.com/board/viewtopic.p...c=1648&forum=2
    "Have a good time......all the time"
    Ian Mac

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 11:09, Ian Mac wrote:
    You could possibly Adapt an answer I gave for a Random League Generator:

    check:

    http://mrexcel.com/board/viewtopic.p...c=1648&forum=2
    Hmm... unfortunately I cannot seem to apply these examples to my 'problem'. I'm sure I"m missing a step or two. I suppose the dynamic number of clients to sample is causing a problm here too.. as they vary every time. From what I can see, the "Rand" or "Randbetween" functions return a single number?

    Any further assistance here would be greatly appreciated.

    Thanks,

    Rob

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if you want me to e-mail an eg I'd be happy to, as long as I've got exactly what you need, the dynamic range won't make a diference.

    BUT do you want the percentage/Number of people to round up or down i.e. do you want 30% of 10 to be 3 or 4

    I'll have to do this at home as I'm just about to leave work
    "Have a good time......all the time"
    Ian Mac

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 12:05, Ian Mac wrote:
    if you want me to e-mail an eg I'd be happy to, as long as I've got exactly what you need, the dynamic range won't make a diference.

    BUT do you want the percentage/Number of people to round up or down i.e. do you want 30% of 10 to be 3 or 4

    I'll have to do this at home as I'm just about to leave work
    That would be great,

    Dont go out of your way to do this either.. . I'll email you some of the specifics. Dont necessarily have to use a percentage either. I will usually know the number to use.. actually now that I think of it, it would be best to use a set, round number (which will be determined ahead of time) (ie, 6), and then generate 6 random, unique numbers from a list of 24 numbers, etc. From there the 6 randomly selected numbers will paste into a specified column, starting at cell C17.

    Thanks again,

    Rob

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi
    Paste this code in a standard module and run off of a button or from the macro menu.
    Will provide 25% of total client picked randomly with no repeats and list alphabetically in row C

    Place this formula in Cell AA1
    =counta(AA17:AA1016)
    This is to track number of clients

    Let me know

    Change all the references to sheet1 or change your sheet name to sheet1

    Sub MopacsRandomizer()
    Dim ArrayCntr As Integer
    Dim ArrayUpperBound As Integer
    Dim NumClients As Integer
    Dim NumberKeeper()
    Dim ThisRndNum As Integer
    Dim AlreadyGotTheNumberBabeeee As Integer
    Dim NumberKeeperCntr As Integer
    Dim PutEmInRowC As Long

    PutEmInRowC = 16
    NumClients = Range("AA1").Value
    ArrayUpperBound = Abs(NumClients / 4) + 1
    ReDim NumberKeeper(1 To 1)

    Do Until UBound(NumberKeeper, 1) = ArrayUpperBound
    NumClients = NumClients - 1
    ThisRndNum = Int(Range("AA1").Value * Rnd) + 16
    AlreadyGotTheNumberBabeeee = False
    For ArrayCntr = 1 To UBound(NumberKeeper, 1)
    If NumberKeeper(ArrayCntr) = ThisRndNum Then
    AlreadyGotTheNumberBabeeee = True
    Exit For
    End If
    Next
    If AlreadyGotTheNumberBabeeee = False Then
    NumberKeeperCntr = NumberKeeperCntr + 1
    ReDim Preserve NumberKeeper(1 To NumberKeeperCntr)
    NumberKeeper(NumberKeeperCntr) = ThisRndNum
    End If
    If NumClients = 17 Then NumClients = Range("AA1").Value
    Loop
    PutEmInRowC = 16
    For ArrayCntr = 1 To NumberKeeperCntr
    PutEmInRowC = PutEmInRowC + 1
    Sheet1.Range("C" & PutEmInRowC).Value = _
    Sheet1.Range("A" & NumberKeeper(ArrayCntr)).Value
    Next


    Sheet1.Range("C17:C1016").Sort Key1:=Sheet1.Range("C17"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Range("A17").Select
    End Sub




    [ This Message was edited by: TsTom on 2002-03-25 12:42 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com