Counting Random generated numbers

[vangogh228]

I am trying to teach the concept of randomness in a college statistics class. I was thinking of writing a macro that would loop a recalculation, and have a =RANDBETWEEN(1,100) formula in a certain cell. Then... and here's the part I cannot figure out... I want to COUNT the number of times each number comes up. That is, I want to have a list of the numbers 1-100 and a counter of some sort for each one. As the random number is generated in the RANDBETWEEN cell, it would be recorded as an occurence in the list. As the random numbers continue to be generated, the totals would continue to add up.

So, for example, the number beside "47" starts at "0". As the macro loops through the RANDBETWEEN recalculation, eventually it hits a "47." The counter for "47" then goes to "1". The looping continues and, when te RANDBETWEEN hits "47" again, the counter goes to "2".

Is this possible? If you have an easier solution, I would be open to suggestions.

Thanks.

Tom

 

[supercharge]

RANDBETWEEN(1,100)
Tom

I'm not sure if that will work but see attached for my solution.

Here is the code:

Sub Auto_Open()
    'Reset all counters to zero
    Range("D3,H3:H102").Select
    Selection.Clear
    Range("H3").Select
End Sub

Sub Go_Click()
    Dim Value As Integer
    Dim Counter As Integer, CompareValue As Integer
    Dim X As Integer, Y As Integer
    Application.ScreenUpdating = False
    Auto_Open
    Randomize
    For X = 3 To 102
        Y = 3
        'Generate a random number from 1 to 100
        Value = Int(100 * Rnd + 1)
        Range("D3").Select          'Not showing all numbers
        'Range("D" & X).Select       'Show all random numbers
        ActiveCell.Value = Value
        Range("F3").Select
        CompareValue = ActiveCell.Value
            
        Do While CompareValue <> Value
            Y = Y + 1
            Range("F" & Y).Select
            CompareValue = ActiveCell.Value
        Loop
        
        'Number matches, increase counter by 1
        Range("H" & Y).Select
        ActiveCell.Value = ActiveCell.Value + 1
    Next X
    Range("H3").Select
    Application.ScreenUpdating = True
End Sub

 

[FlyerMike]

Sounds like you need an array of a User Defined Data Type. This array consists of integers (generated by your RANDBETWEEN) and presumably a long integer (TALLY) that stores how frequently the integer has been chosen.

Public Type TallyCount
    iMember as Integer
    lTally as Long
End Type

Public Sub IncrementTally(ByRef iChosen as Integer, _ 
                                    ByRef MyData() as TallyCount, _ 
                                    ByRef lIndex as Long)
On Error goto Err_IncrementTally

    Dim bNewEntry as Boolean
    Dim i as Long

    bNewEntry = True

    'Check current members of array for item returned by RANDBETWEEN
    For i = 1 to lIndex
        If MyData(i).iMember = iChosen Then
            bNewEntry = False
            MyData(i).lTally = MyData(i).lTally + 1
            Exit For
        End if
    Next i

    'Add new entry to array if iChosen has not yet appeared
    If bNewEntry Then
        lIndex = lIndex + 1
        Redim Preserve MyData(lIndex)
        MyData(lIndex).iMember = iChosen
        MyData(lIndex).lTally = lTally + 1
    End if

Exit_IncrementTally:
    Exit Sub

Err_IncrementTally:
    Call ErrHandler("IncrementTally routine",Err.Number,Err.Description)
    Resume Exit_IncrementTally

End Sub

 

[vangogh228]

Thanks, fellas. I have not had a chance to try these yet, but I certainly will. I appreciate your help with this!!!