This may be a bit pretentious on my part. After all, Excel has been around for decades. Personal computers have been around only a little bit longer than that. We use them for everything from business to pleasure. There’s no reason for me to think that the reader hasn’t (or couldn’t, if so inclined) obtain the following information on using Excel to improve his/her D&D game.
But if it is out there, I haven’t found it yet. Sure, I’ve found all sorts of excellent resources for using Excel. I’ve found lots of resources for improving the game. I’ve even found a few D&D Excel files, though only a couple were worth keeping. Part of it is my anal retentive approach – I prefer my resources formatted in a very specific way – but part of it, I think, is that people generally don’t grasp how Excel works for the DM.
Let’s be clear about this: we have to have a specific goal in mind. I’m a nerd/geek/whatever-label-is-cool-these-days, and I enjoy coding and writing programs. I enjoy identifying a problem and then seeking a solution to that problem, even if the process is more complicated than the end result. (Indeed, I believe that the best results are the ones we arrive at through hard work.) But the goal in this exercise is not to do hard work for the sake of doing hard work. We need to identify a problem and seek a simple solution, ideally one that’s flexible so that we can expand it without too much effort.
For this example, I’m thinking of random treasure tables. A few months back I started transcribing the magic items from the Enclopedia Magica into a database so that I could generate random items quickly. The project was set aside when I ran into the problem of methodology – how, exactly, do you determine which magic items are more common than the others? Is there any relationship between location, like dungeon or city, and the items produced by the table? How do you decide whether an item is being used by an NPC or just lying in a treasure chest somewhere? – but the process I used to create the tables is what I want to look at here.
Let’s start with a simple table, one that lets us create a simple result. In a blank Excel file (or Calc, if you prefer the open source version), enter the following data:
Keep in mind that we can change this data at any time. For now, it’s sufficient to recognize that column A represents the roll of a die and column B represents the result of that roll – a simple table. What we need to do next is make it into an Excel table:
Tables are great because they let us make changes very quickly, and when you have very large tables, this saves a lot of effort. Before I explain, however, let’s create the output for this table. In a nearby blank cell, enter the following formula:
=INDEX(Table1, RANDBETWEEN(1, MAX(Table1[Roll])), 2)
“INDEX” is an Excel formula that lets you look up information across a table by referencing a column number against a row number. “Table1” is the name of our table (which we can change if we want). “RANDBETWEEN” is a great function for generating random whole numbers between the listed minimum and maximum. “MAX” is a function that looks at a given range of cells and returns the highest numerical value. Now, this last function is keyed to our table (to column “Roll”) and this is the reason that tables are awesome. If, down the line, we decide that we need to add to our item table, all we have to do is add some data to the cells at the bottom and any formulas that reference the table will automatically adjust themselves. We could have hundreds of calculations keyed to hundreds of tables, and all it takes to update them all is adding data to the appropriate table.
Another thing tables do for us is they recognize calculations in their columns and can auto-populate a column, which again saves us the effort of changing every single cell if we want to change the whole thing. Let’s update our table – after all, we want each item to have a different chance of coming up.
In cell A2, where we have a “1”, enter the following:
You’ll note that the cell now has an image next to it. Left click on it to bring up a list of options, then select “Overwrite”. You’ll note that all the cells were changed to the formula, but this is only the first step. The next thing we need to do is modify cell A2 to the following:
=SUM(A1, RANDBETWEEN(1, 20))
Now we should have an ascending list of values.
Keep in mind that as long as this column has a formula in it, it will recalculate every time you change the document (or when you press F9) and we might not want that. If that’s the case, highlight the column, copy and paste the values back into the same column. Or, if you already have figures for your table, just enter those numbers as written. Either way, keep in mind that when we expand the table, the only cells that auto-populate with data are the ones with formulas in them.
The last thing we need to do, after setting incremental values to our table, is to adjust our Index function. For that, we need to modify the formula in D2 to the following:
=INDEX(Table1, MATCH(RANDBETWEEN(MIN(Table1[Roll]), MAX(Table1[Roll])), Table1[Roll], 1), 2)
All we’ve done is added the MATCH function, which lets us tell the formula to use a reference point – in this case a random value between the MINimum and the MAXimum numbers in column “Roll” – to look up a value in column 2. Finally, the “1” that I’ve circled is important because it tells the MATCH function that what we’re really looking for is the number we want or less than that number; that way, when the formula checks for 19 but can’t find it because the next number on the list is 20, it’ll default to the 15 that’s below the 20.
So this is just the beginning; my next post will be about using Index formulas inside of tables to create a series of self-referencing tables. The end result should be an output where the DM has only to press a button to generate a meaningful result for a game.