Random is not randomizing very well

[ss123]

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:

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

 

[MARK858]

Does the below make any difference

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

 

[StephenCrump]

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:

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]

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]

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:

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]

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