Dice simulation with Spreadsheets

Understanding the math behind dice is important for any game designer. In this article I describe how I used a spreadsheet to simulate dice rolls. The spreadsheet (in OpenOffice and Google Documents formats) and formulas used are available in the article as well so you can experiment yourself!

 

In games where for example a dice is rolled to advance spaces, the average roll of the die plays a large part in the number of turns it is going to take for a player to reach the goal. Most players (especially experienced players) use the rules of probability when determining their starting villages in a game of Catan: as close as possible to a piece of land with the number 7 on it.

There are many well written articles about probability and dice. Recommended reading is Edward Collins’ explaination about the most common mistakes when rolling multiple dice, or this page by Jo Edkins with an interactive table that visualises dice combinations in a grid.

Enter… custom dice!

The Game Crafter sells special blank dice. These dice are designed for the special stickers so you can decide yourself what the dice are used for. For example, for my game Dr. Pergias’ Race Across the Continent, I used the dice as a way to determine what resources a player would receive in a turn. A lightning bolt would count as “1”, a drop of oil as “5” and a cloud of steam as “10”.

To make the game less random- but still random enough- I decided to let the player roll 5 dice and pick the 3 dice the player liked the most.

But how to distribute the values over the dice? I could have done some fancy math to determine the average roll. Every change or tweak would mean I had to recalculate everything. Also what if I wanted to add another die? Or let the player pick just two instead of 3 dice? Or would it be better to have the player leave out just 1 bad roll?

The simulation

For my game I wanted to end up with an average yield of about 20 energy- this would enable the player to travel about 6 squares on the game board. Some game mechanics would speed the player up considerably, but the 6 spaces was a nice average.
My simulation would live inside a spreadsheet. A couple of cells to represent the sides of each die and a string of randomly picked values:

The advantage was that I could tweak the dice on the fly and directly see the results! Take a look at the Google Documents sheet here or download the Open Office Calc document.

How it works

First the die is created, in my example I used the 6 cells in column B to represent the sides of my die. So the cells B2, B3, B4, B5, B6 and B7 each hold a value of my die:

B
2 1
3 1
4 1
5 5
6 10
7 10

Now to simulate a dice roll use this formula:

Let me explain this bit: INDEX returns a value within a range of cells – in this case the range $B$2 to $B$7 (the $ signs indicate that this is a “fixed” range). The RANDBETWEEN part generates a number with the low boundary 1 and the upper boundary 6- on of the sides of our die.

By dragging that formula over a bunch of cells you generate random dice rolls for those cells, ready to do some in depth analysis!

Putting it together

Now we know how to create our virtual die and rolling one. In my example there are 5 dice I want to simulate. Since I want to change the sides of all those 5 dice at will I created 5 dice – one in each of the columns B to F.

Next I simulated 90 dice rolls for each die as you can see in the image above (starting from row 9).

Now I was interested in the sum of the 3 highest rolls, this formula takes the highest 3 rolls from the cells in the range and adds them up:

This way it is possible to get the average over 90 dice rolls where the player picks the highest values! (Note that in the OpenOffice example I had to create separate columns first for the highest die results and add them later).

The fun part is where you play with the values on the dice. Why have 5 identical dice? Maybe a die with 1,1,1,5,5,10 and another with 1,5,5,5,10,10?  You can also change the formula to select the top 2 or top 4 rolls (essentially discarding the worst die result).

Just play with the values and see what happens! Download the OpenOffice example document.

Advanced stuff…

For the Doctor Pergias game I added special values to the dice. Some dice enable the player to double the yield from the other two selected dice. Another special die side made lightning as valuable als a steam cloud…

To accomodate for this there is more spreadsheet magic required, perhaps stuff for another article!

2 responses on “Dice simulation with Spreadsheets

  1. Paul Owen

    I’m very familiar with using spreadsheets to do probability analysis for game designs, but you’ve introduced three new Excel functions to me that I didn’t know about before. Thanks for that!

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *