Spreadsheets: A game designer’s best friend
Seriously, if you haven’t fallen in love with spreadsheets yet, you’re doin’ it wrong. I’m currently in the middle of figuring out a multiple-week game loop with really tight targets. How the heck would you ever get this done without a well-constructed spreadsheet? Trial and error would take forever. Guesstimates wouldn’t get you anywhere near what you wanted. No, you need constantly-updating formulae and graphs and colours and stuff. The difference it makes in your capabilities is insane.
A quick example
Say you have an enemy – a cute little slime-creature. And when he’s defeated, he drops a macguffin – but occasionally! Players need ten of these macguffins to progress to the next area, where there’s a boar who drops a similar macguffin. And in the next area, there’s the final creature – a gargoyle – and ten more macguffins to collect.
Three monsters, three macguffins. We want this area to be challenging, but not too frustrating, so let’s set some base assumptions: If it takes less than 100 encounters, it’s not challenging enough. If it takes more than 300 encounters, it’s too frustrating.
Now, we could just throw some numbers onto those creatures and hope, but I’ve got a better way. Let’s set up a spreadsheet with some intuition-based numbers and see how close we come.
Table 1: Drop Rates
The first thing we need to figure out is how often a player is going to get a macguffin drop from a creature. To do that, I’ve created the following table:
Column A is the name of the creature the player will be encountering. B is the chance they will beat that creature when fighting them. C is the chance they drop the macguffin they are looking for. And finally D is the number of expected macguffins from each encounter. So far, we can see that for every time you fight a slime, you can expect to get 0.07 of a macguffin. Seems pretty low, but we’ll find out just how low in a second. Let’s make a second table.
Table 2: Encounters Needed
Column A has the same creature names as before. B is the amount we need to collect before we can progress. And C is the number of expected encounters a player will need to go through to get the amount listed in column B.
And WOW are those numbers high! Seems our intuition was off, and it’s time to play with the numbers a bit to balance this out. We have some options:
- We could increase the drop rate of the macguffins. This will not only speed up the collection, but also reduce variance for players. (Variance is a topic for another time, but in short, you want less of it. High variance means that you’ll have a lot of players who fall outside of your expected numbers, which is either exceedingly frustrating or boring for those players.)
- We could decrease the difficulty of the encounters. This will allow players to collect secondary assets (gold, experience) more quickly while speeding up collection as well.
- We could decrease the number of macguffins needed. Psychologically, collecting 10 macguffins at a 10% drop rate feels a lot less like a chore than collecting 100 macguffins at a 100% drop rate.
And that’s outside of doing something crazy tricksy, like adding or removing more creatures, allowing players to buy or craft macguffins, etc. So let’s play with the numbers some, and see if we can come up with something a little more fun.
Table 3: Some Interesting Numbers
It only took me about five minutes of tweaking to come up with this, and I can already tell that it’s a lot better. For one, it only takes 191 encounters to resolve – almost exactly in the middle of our 100-300 range. For another, see how the Encounters column in the second table gently rises? It didn’t when I first started playing around with the numbers, but I quickly saw how going from 50 encounters to 25 encounters to 125 encounters would be very weird, and altered my values to suit.
Of course, you’ll still want to play-test this to ensure it’s actually fun when you play it, but this is a much better starting point for your playtest than our original numbers, and it only took five minutes to find out, and five minutes to improve it a vast amount.
Hopefully you can see how much of a difference a good spreadsheet can bring to a game developer’s toolkit. I’ve still got a few more concepts I want to cover regarding spreadsheets (the LOOKUP function, conditional formatting, etc.), so stay tuned!