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:
"=RANDBETWEEN(1,6)+RANDBETWEEN(1,6)+RANDBETWEEN(1,6)".
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.
While this is useful, it's a really, really poor substitute for actually rolling some dice. Whatever you do, don't ever lose the soul of what D&D is and rolling dice with a group of friends is it.
ReplyDeleteFor my recent megadungeon work creating literally thousands of rooms, I've used Excel to randomize several things, and initial room description is just one of them.
DeleteI then go through and begin to fiddle with what excel produced, moving things around and making them 'fit' with factions, unique specials and such. Even after this I still use Excel - the room descriptions are in Excel and it tallies how many rooms and what percentage are monsters, monsters with treasure, treasure by itself, tricks, traps, and specials. Different dungeon regions are, by my choice and design, weighted toward one or the other sometimes. But its a good check on my work to see if a dungeon area is too monster heavy, too empty, or if there is little to no treasure.
What I meant to implement but never did was to sum up how much treasure and experience was on each level.
None of this was done at the table. It was just to speed up the initial process of creation for the over-worked but still creative DM, and not to replace any of the magic that happens at the table.
Wizardawn has a great utility for generating random dungeons with just about any variables you want. The utility's limit is in the monsters available-- but that's something you can change on the fly with a (legitimate, not agency-robbing) palette shift.
ReplyDeleteThis is a good post - while rolling the dice is important when sitting face to face, the GM already has enough to do and using excel to get things moving and grooving is something i've been doing for ages (that and tablesmith). also with excel you can make your own charts and tables quite easily. I was going to comment about the VLOOKUP function but it appears as though this is already in the works.
ReplyDeleteSuper useful post. I look forward to part 2.
ReplyDeleteI'm a big proponent of rolling dice in the open, in front of the players. There's some old post here called 'Die Strahd Die' where my players gakked the Lord of Ravenloft because I made an unlucky roll. Good things, bad things, amazing things happen if you let the chips fall.
ReplyDeleteOn the other hand, no need to waste valuable table time on things like the hit points of 20 wandering orcs when a button push gives you everything you need. Glad some folks found it useful.
I'm looking forward to your discussion on VLOOKUP.
ReplyDeleteI think though that you mean omit the quotation marks, not omit the parentheses.
Yikes. Fixed it - good catch. The parentheses are kinda important.
DeleteI'd really appreciate a sample spreadsheet available for download on google docsor the like!
ReplyDeleteOne point that confuses me is the point you make about leaving a free cel open and if you type a letter or number in it, it rerolls all the random numbers again. My understanding of it, if you type something in an empty cel, it puts that in that cel without changing anything else in any other cel unless there is a formula specifically entered to do so. Can you clarify this point. My experience with Excel is limited, so I do welcome these posts to increase my knowledge.
ReplyDeleteYou can change the calculation option to manual then recalculate (reroll) when you want to. In Excel 2013 it is under the Formulas tab, I don't recall from previous versions.
DeleteYeah, the way the RANDBETWEEN function works, any time anything in the spreadsheet changes, new random seeds are generated and all the random values are done over. It's a feature or a bug, depending on what you're trying to do. As Rev. Dr. Dale points out, there is an option to turn it off (set workbook calculations to manual under formulas).
DeleteI usually keep a blank cell somewhere where I change a value any time I want to reroll all the numbers.
You can also use F9 to reroll them all.
DeleteThanks for the information. I knew I could do stuff like this in Excel, I just wasn't sure of the formulas.
ReplyDeleteI have been getting a lot of use out of excel for generating attributes for large numbers of NPCs (bandits, mercenaries &c).
ReplyDeleteI also have a bunch of excel gaming sheets I hope to clean up and share some day soonish. I have one that generates Cleric & M-U spell scrolls using the Labyrinth Lord rules for number and level of spells and Lamentations of the Flame Princess for the spell lists. It also has a grimoire maker of my own devising.
That's good stuff, I usually generate bandits and similar parties using Excel as well. I took pretty much the whole Seclusium of Orphone and reduced it to a bunch of janky Excel tables so I wouldn't have to leaf through the book. I like the idea of auto generating spell books and scrolls.
Delete