Random is not randomizing very well

ss123

New Member
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:

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
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``````

StephenCrump

MrExcel MVP
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
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
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.

StephenCrump

MrExcel MVP
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

Replies
4
Views
195
Replies
5
Views
437
Replies
7
Views
124
Replies
8
Views
186
Replies
14
Views
347

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.

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

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