Random is not randomizing very well

ss123

New Member
Joined
Mar 4, 2014
Messages
11
Hi,

I have created a board gamer's aid in a Dice rolling workbook. Essentially it rolls a number of die 10's as specified by the user and shows the sum of the values rolled. The game it is doing this for requires massive amounts of die 10's to be rolled. Everything looked OK when I was choosing lesser numbers of die 10's to roll. But when I tested 30, for example things started to get flaky. Specifically with a die 10, results of 1 to 10 can be rolled. But for some reason the random generator was not rolling anything above 200 in sum. Confused, I looked at the average (sum/number of dice) and it consistently stayed between 5.0 to 5.9. Given that 30 die 10 can result in maximal sum of 300 and a minimal sum of 30, this did not make any sense that I could not get a sum of higher than 200.

I know that average between 30 and 300 is 165 and that equals an average die roll of 5.5. But averages tend to go up and down and not stay fixed at certain scale.

I checked on Google but could not find any specific information on the 'rnd' function in VB other than the generic stuff barfed out by Microsoft. I remember reading that Excel creates a pseudo-randomized number but I am not sure if this implies that I cannot use Excel for creating randoms numbers in dice rolling applications. Or perhaps I am not understanding the mathematics of it all.

Other than the non-random issue, my code works perfectly as intended.

My code:

VBA Code:
Sub Roll10()

Dim neXx As Integer ' the for/next loop initiator
Dim numbOfDicE As Integer ' the number of dice that will be needed to be rolled
Dim totalSumMed As Integer ' the total sum of the dice
Dim rolLED10 As Integer ' the actual d10 results

numbOfDicE = Sheets(1).Cells(7, 10).Value ' loads how many dice I need to roll into a for/next loop

For neXx = 1 To numbOfDicE
    rolLED10 = Int((10 - 1 + 1) * Rnd + 1)
    totalSumMed = totalSumMed + rolLED10
Next neXx

Sheets(1).Cells(7, 16).Value = totalSumMed

MsgBox "Average rolled: " & (totalSumMed / numbOfDicE)

End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,959
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Does the below make any difference

VBA Code:
Sub Roll10()

    Dim neXx As Integer                          ' the for/next loop initiator
    Dim numbOfDicE As Integer                    ' the number of dice that will be needed to be rolled
    Dim totalSumMed As Integer                   ' the total sum of the dice
    Dim rolLED10 As Integer                      ' the actual d10 results

    Static bIsRandomized As Boolean
    If Not bIsRandomized Then Randomize: bIsRandomized = True

    numbOfDicE = Sheets(1).Cells(7, 10).Value    ' loads how many dice I need to roll into a for/next loop

    For neXx = 1 To numbOfDicE
        rolLED10 = Int((10 - 1 + 1) * Rnd + 1)
        totalSumMed = totalSumMed + rolLED10
    Next neXx

    Sheets(1).Cells(7, 16).Value = totalSumMed

    MsgBox "Average rolled: " & (totalSumMed / numbOfDicE)

End Sub
 
Solution

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
With 30 rolls, there is a ~1.4% chance of 200+, so with 100 iterations you'd expect to see 200+ happening quite often.

Here's some quick code to dump all results:

VBA Code:
Sub Roll()

    Dim N As Long, Repeats As Long, Sides As Long, i As Long, j As Long
    Dim Results() As Long
    
    N = 100: Repeats = 30: Sides = 10
    ReDim Results(1 To N, 1 To 1)
    Randomize
    
    For i = 1 To N
        For j = 1 To Repeats
            Results(i, 1) = Results(i, 1) + WorksheetFunction.RandBetween(1, Sides)
        Next j
    Next i
    
    Range("A1").Resize(N).Value = Results

End Sub
And here's a website that neatly illustrates the probabilities: Troll dice roller and probability calculator
(Type sum 30d10 in the box, and click the Calculate Probabilities button)
 

ss123

New Member
Joined
Mar 4, 2014
Messages
11
Actually decided to roll my arms off with 30 physical dice. Ten attempts later, my average was 5.68 which falls within the range of what Excel was calculating for me once I added in your two lines of code.

So in other words - thank you for your help, greatly appreciated! :)
 

ss123

New Member
Joined
Mar 4, 2014
Messages
11
With 30 rolls, there is a ~1.4% chance of 200+, so with 100 iterations you'd expect to see 200+ happening quite often.

Here's some quick code to dump all results:

VBA Code:
Sub Roll()

    Dim N As Long, Repeats As Long, Sides As Long, i As Long, j As Long
    Dim Results() As Long
   
    N = 100: Repeats = 30: Sides = 10
    ReDim Results(1 To N, 1 To 1)
    Randomize
   
    For i = 1 To N
        For j = 1 To Repeats
            Results(i, 1) = Results(i, 1) + WorksheetFunction.RandBetween(1, Sides)
        Next j
    Next i
   
    Range("A1").Resize(N).Value = Results

End Sub
And here's a website that neatly illustrates the probabilities: Troll dice roller and probability calculator
(Type sum 30d10 in the box, and click the Calculate Probabilities button)
Interesting. I ran the code and came out with three series each and the average yet again came out to around 165 which divided by 30 equals 5.5. Yet none of the sums was greater than 200. Maybe I should have not clicked on Resolved so quickly...

Oh well. I think that the randomize function in Excel is not too good with large amounts of iterations (which I had read about elsewhere). In my own physical attempts to roll 30d10 ten times, I got at least two iterations with a sum of 200 or greater. However my average was 5.68.

Thank you for your input!
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,117
Office Version
  1. 365
Platform
  1. Windows
the average yet again came out to around 165 which divided by 30 equals 5.5.
which is exactly what you'd expect.

You'll get some variability with only 100 iterations. Here's a sample comparison I just ran: 100 vs 10,000

1608590289364.png
 

Watch MrExcel Video

Forum statistics

Threads
1,126,999
Messages
5,622,118
Members
415,878
Latest member
jjj12345

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