Monday, April 21, 2014

Excel and Random Dungeon Stocking

If you're familiar with a programming language, you can go pretty far in automating a random dungeon .  Not everyone is a programmer, but lots of people use Microsoft's Excel - it's been a workplace standard forever, and lots of students get access to Office at university.   Here are some tips on generating basic content with Excel that anyone can try.

The 1980 Moldvay edition red book for D&D has a simple stocking approach on page B52 - one d6 roll for room contents, and a second d6 roll for treasure.  Today we'll automate those rolls in Excel.  The first Excel function we'll look at is RANDBETWEEN - it generates a random number between two end points.  For instance, type "=RANDBETWEEN(1,6)" in an open cell in Excel to generate a number from 1 to 6 (omit the quotation marks when you put it in Excel).

Because of how quickly you can copy formulas between cells in Excel, this is also handy for generating lots of random numbers without rolling dice.  I always have a spreadsheet open to the side when I'm running a game, with around 20 cells of "=RANDBETWEEN(1,8)" to generate lots of hit dice for wandering monsters.  I have similar lines for other common hit dice values - "=RANDBETWEEN(1,8)-1" for those 1-1 HD monsters like goblins (treating zeroes like ones) or "=RANDBETWEEN(1,8)+1" for hobgoblins.  You can add multiple results together -  "=RANDBETWEEN(1,8)+RANDBETWEEN(1,8)" adds two 8-sided rolls together for a 2 HD monster.  You can use this approach to generate ability scores on the fly for NPCs - a formula like the next one generates an ability score between 3 and 18:


I leave one cell free on the sheet where I type a letter or number - you'll see that whenever you make a change to that cell, all of the random values reset, effectively 'rerolling' all the random numbers.  There are many rolls that are important to roll in front of the players - attack rolls and monster saving throws, for instance.  But for set up items like hit points, it's great to have this kind of thing handy while you're running a session.

Getting back to the dungeon stocker, let’s take a look at using the IF function in Excel (a simple type of if-then-else you'll see a programmer use) and marry it up with our random roll.  We can nest a couple of "If" statements to generate room content in line with Moldvay's tables.  Assume you've put your 1-6 die roll in cell A1.  In cell B1, go ahead and put in a formula like this:  "=IF(A1<3,"Monster",IF(A1=3,"Trap",IF(A1=4,"Special","Empty")))"

Breaking it down step by step, the formula first checks to see if the dice roll is less than 3 (meaning a result of 1 or 2) in which case the value is "monster".  If it's not a result of 1 or 2, it checks to see if the result is 3, yielding "trap"; it goes on to check if the value is 4 (special) and anything else is "empty".  Simple, right?

We need to add a second dice roll for treasure in cells C1 and D1.  In C1, go ahead and put another d6 roll - "=RANDBETWEEN(1,6)" - and in cell D1, put a slightly more complicated "IF" formula like this:

"=IF(AND(B1="Monster",C1<4),"Treasure",IF(AND(B1="Trap",C1<3),"Treasure",IF(C1<2,"Unguarded Treasure","No Treasure")))"

By using IF and AND, we're able to evaluate two results at the same time - the room contents we generated previously and the new dice roll - to see if treasure is present.

To finish up today, go ahead and copy the four cells down the page.  An easy way to do it is to highlight the four cells containing formulas (A1, B1, C1, D1).  In the bottom right corner of cell D1 there is a little square in the highlighted outline - by clicking on it, holding, and dragging your cursor down the sheet, you can auto-copy the formulas down the page.  This way you can generate the contents of lots of rooms at once.

Even if you don't plan to do any random dungeon stocking, the random number generation available in Excel is really useful.  Let me know what you think.  In part 2 I'll cover how the VLOOKUP function can be used to retrieve actual content results from our content tables to make the stocker provide a little more value.