Something I’ve wanted to post about for a while but never seem to get to is the RANDBETWEEN function in Microsoft Excel. This makes it easy to simulate dice rolls and I use it extensively when simulating rules changes such as Roll to Advance or combining monster attack routines into one combined abstract attack. I create a few formulas, drop in the numbers, and run it hundreds or thousands of times to see what sort of results can be expected. If things don’t seem quite right, I simply adjust the numbers a bit and run it again.
It’s one thing to calculate the mathematical averages and ranges and compare, but it’s much more enlightening to see real-world results. I use spreadsheets and RANDBETWEEN to do this without having to roll a bazillion dice.
RANDBETWEEN is easy peasy to use. The format is RANDBETWEEN(x,y), where x is the lowest integer you want and y is the highest. Simply enter the low end and high end of the range you want, and it spits out a number. So a formula for a d6 would look like this in an Excel cell:
That’s all there’s to it. The cell will display a number between 1 and 6. Hit F9 to recalculate your sheet and it will change. It will also recalc if you change other cells in the sheet unless you disable that.
Now, it’s important to remember that it’s an even probability for all numbers in the specified range, so =RANDBETWEEN(3,18) will not simulate 3d6. To do that, you need to simulate 1d6 three times and add the results. A formula for that would look like this:
which translates to 1d6+1d6+1d6
Basically you need a RANDBETWEEN for each die you roll, adding them together with plus signs. I usually utilize more parentheses in the formula to keep things clear and make it easy to do calculations on grouped dice, but this will work for simple rolls.
If you use spreadsheets much, this is obviously simple. If you’re not spreadsheet users, though, it can seem a bit much. Once you use it a few times, it will make complete sense. Sort of like descending AC.
Using this function along with other Excel functions and capabilities makes it a snap to build almost any simulations or generators you want. Using LOOKUP functions even allows you to drop in a table of results, such as a random encounter table, and have Excel roll the dice and tell you what monster shows up for the party.
A few final notes:
First, RANDBETWEEN works out of the box with Excel 2007. It is also included with older versions of Excel, but only as part of the Analysis ToolPak add-in. To enable this included add-in, go to the Tools menu, select Add-Ins, and choose Analysis ToolPak. Once that is enabled, RANDBETWEEN should come up just like any other function. I did not need my installation CD when trying it on a machine with Office 2003, but that probably depends on what was chosen during installation.
Second, OpenOffice apparently has a RANDBETWEEN function that works pretty much the same as Excel’s. The format appears to be RANDBETWEEN(bottom; top), using a semicolon instead of a comma like Excel. I haven’t tried it but I imagine it’s the same thing.
Finally, I don’t know if there are a lot of Excel-using gamers out there, and if there are they may all be far more knowledgeable spreadsheeters than I am. But if there is interest, I could probably put up some basic tutorials about how to use Excel for gaming purposes with some examples of the sorts of things I do. If this sounds like a good idea, let me know in the comments section.