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:

=RANDBETWEEN(1,6)

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:

=RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)

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.

8 Comments to “RANDBETWEEN”

  1. I just thought I’d chime in to say that RANDBETWEEN seems to work fine out of the box (and with the same parameters & formatting as Excel) in the current version of Numbers, the spreadsheet software that’s part of Apple’s iWork suite.

  2. Also, thanks for pointing this out. I do love spreadsheets, but somehow I’d never encountered this function before. I suspect that had something to do with my creaky old copy of Excel not having it. But now that I know about it, you can bet I’ll be using it.

    Cheers!

  3. The Basic Fantasist says:

    “Sort of like descending AC.”

    Nice crack. 😛

  4. lige says:

    That’s Awesome! I’ll have to find a use for it.

  5. Alex says:

    I’ve used this function for “quick creation” Labyrinth Lord characters — rolling up attributes and starting gold for my players and NPCs. I normally have a stack of 5 rolled up in sequence so that if the first set is too “penalty-laden” (i.e. 2 or more minuses and no bonuses), I quickly go to the next set.

  6. JB says:

    Just wanted to say, I love randbetween…it’s been a godsend at times…
    : )

  7. jstater says:

    And when you mix RANDBETWEEN with a few IF statements and VLOOKUP, you can turn Excel into a pretty good generator of random goodness. I have a random treasure generator based on C&C and a massive random sandbox generator as well. I’m about 85% done with one to make random esoteric monsters.

    • Kilgore says:

      You bet. I’ve got a S&W White Box NPC generator that whips up NPCs with class, level, hit points, weapons, and magic items one thousand at a time. Still needs a little tweaking but I’ll get to it some day.