Creating a Random Number Generator

neonexistence

New Member
Joined
Feb 25, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,260
Office Version
  1. 2010
Platform
  1. Windows
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
1614319442556.png


and heres the code
VBA 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

Well-known Member
Joined
May 20, 2004
Messages
3,260
Office Version
  1. 2010
Platform
  1. Windows
and this is from the wish list :)
1614320331515.png


and the code:
VBA 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

Well-known Member
Joined
May 20, 2004
Messages
3,260
Office Version
  1. 2010
Platform
  1. Windows
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)

1614321120672.png


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:
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,922
Messages
5,621,620
Members
415,847
Latest member
AlpinoHirsch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top