Creating a Random Number Generator

[neonexistence]

Hi there,



I am a high school teacher and am looking at creating an excel sheet that will act as a random number generator. As a start, I would like the code to randomly generate a number from a list of values and record it in column 1. I would then like it to continue recording each trial underneath in column 1.



Some extensions of this (not necessary but helpful) are:

1. The probability of each outcome is not equal (sum of rolling two dice)

2. Can quickly run x amount of trials (1-1000)



I am going to be putting a frequency table next to it, so the macro would not need to count the number of 1's, 2's etc. but merely copy them down in a big list.



Any feedback on how to achieve this would be greatly appreciated!

 

[diddi]

i was a chalkie too once, but i saw the light!

heres sample... The values from which your randomly chosen number is selected is Col E. C1 is the number of trials (selections) rolls etc

and heres the code

Sub PickRandomFromE()
    Dim Trial As Long, NumTrials As Long, LastRow As Long
    NumTrials = Cells(1, 3)
    LastRow = Cells(Rows.Count, "E").End(xlUp).Row
    
    For Trial = 1 To NumTrials
        Cells(Trial, 1) = Cells(1 + Int(Rnd() * LastRow), 5)
    Next Trial
End Sub

 

[diddi]

and this is from the wish list

and the code:

Sub RollTwoDie()
    Dim Trial As Long, NumTrials As Long, LastRow As Long
    NumTrials = Cells(2, 5) ' the number if trials is in E2 this time
    
    For Trial = 2 To NumTrials + 1
        Cells(Trial, 1) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
        Cells(Trial, 2) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
        Cells(Trial, 3) = Cells(Trial, 1) + Cells(Trial, 2)
    Next Trial
End Sub

 

[diddi]

and heres the super version to add on to the previous part in the cells i have used (no formulae, and leave Q2:Q12 empty once you have the graph working)

run the code and watch the chart become more binomial in distribution (live) as it calculates. cant post a vid

and your code is here:

Sub RollManyTrials()
    Dim Trial As Long, NumTrials As Long, LastRow As Long, RunNumber as Long, Row as Long
    NumTrials = Cells(2, 5) ' the number if trials is in E2 this time
    
    For RunNumber = 1 To Cells(2, 10)
        For Trial = 2 To NumTrials + 1
            Cells(Trial, 1) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
            Cells(Trial, 2) = 1 + Int(Rnd() * 6) 'a die has 6 sides i assume
            Cells(Trial, 3) = Cells(Trial, 1) + Cells(Trial, 2)
        Next Trial
        For Row = 2 To 12
            Cells(Row, 17) = Cells(Row, 17) + Cells(Row, 8)
        Next Row
        Calculate
    Next RunNumber
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Creating a Number Generator - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.