Spreadsheets – a word that drives terror or delight into the heart of many a game designer.
I was a Business major in college, and it was a class on entrepreneurship where I got to be really comfortable in spreadsheets. If you already have a bit of knowledge of spreadsheets, this post is for you (if you feel you’d like a bit of catch-up, head here for a primer)
Dangit, Jim, I’m a game designer not a number cruncher!
As they’re most commonly used by designers, spreadsheets do four major things:
- Organize data
- Help with balance
- Make it easy to share data with collaborators, publishers, graphic designers, etc.
- Make it possible to import data into services like Component.studio or Nandeck
Keeping you organized is their first major benefit, and being able to share or import data is great. It’s the second part I’ll be doing a deeper dive into today.
Open this template worksheet now to follow along. (Start in the ‘without cost’ tab.)
For this post I’m going to be in Google Sheets, mainly because it’s free and online. By all means feel free to use Microsoft Excel, OpenOffice Calc, or whichever program floats your boat. They all work basically the same way.
Behold, the spreadsheet:
For this example, I have a set of 20 cards, each with a Attack, a Defense, and some sort of Special effect. This isn’t created with any specific game in mind, but it follows the same patterns I follow in a real-world game. The rest of the stuff is just here for tracking / internal use (like the ID number) or balancing.
Each row (columns A-L) represents one card. While there’s other details that will be on the card (like art), only the details with a green background actually make it on the card. I’m highlighting this to mention one major fact: spreadsheets can contain everything related to a game’s numbers, including how to derive what goes on the card.
Assumptions and systems
Starting out, the numbers we’re putting into the spreadsheet can be assumptions or guesses. Totally fine. As you gain experience, your sixth sense builds in that experience into these guesses. As you playtest, you’ll confirm or refute those assumptions, then build a better model for the next playtest.
In short, this is a skill you can get better at.
AV, DV, and SV?
Attack Value, Defense Value, and Special Value.
The easiest way I’ve found to balance something is to assign a value to each elements in a game. This value is assigned to the various elements of a game, and the total value is basically a rating of how good a card is, compared to other cards. More powerful actions are assigned a higher value. A card that deals more damage (or offers a higher defense) will usually have a higher value.
Element * weight = value
Alongside this value is a weight – a chance to buff or nerf specific types of elements. Give an element a higher weight to make it more valuable, or give it a lower weight to make it less valuable. In this example (and more for the sake of showing how things works), I’ve given Attack a weight of 0.5 (cell O1) and Defense a weight of 2 (cell O9).
In other words, whatever the attack number is on the card is multiplied by 0.5 to get the attack’s value.
It’s worth noting these weights are completely arbitrary – you might start with each weight being 1 if you wanted. This weighting is how you put your thumb on the scale – if you want to make defense feel more valuable without changing the number on the card, give defense a higher weight.
How do I assign values to non-numerical things?
Things like the Special Value (or any text effect) becomes a judgement call. In this example, imagine you’ve written a bunch of special effects (this is column J where it just says ‘text’). Whatever they are, imagine rating each one on a scale of some kind – could be 1 to 10, 1 to 5, or whatever.
The value given to each card could be as simple as your rating of that effect – 1 to 10, 1 to 5, or whatever. There’s tons of ways to weight this, however. In this example, I’m using triangular scoring – a rating of 1 equals a value of 1, a rating of 2 equals a value of 3, a rating of 3 equals a value of 6, and so on. (If this isn’t granular enough, the system can expand into decimals easily enough – a rating of 2.5 can equal a value of 4.5, for example).
Why would I use triangular scoring here? Perhaps some of these special effects might be more tactical in use, or only usable at specific times in the game. The more powerful ones are… more powerful, but maybe they can be used anytime. Right now, an effect rated a 4 would have a value of 10, while an effect rated a 1 would have a value of 1… but is the former 10 times more powerful than the latter? This is where playtesting comes in. Perhaps you discover an effect rated a 4 is about 7-8 times better… or 20 times better. Either way, change the weight and the rest of the puzzle begins to sort itself out.
Calculate the cost
One big reason to create values like this is to establish a fair cost for a card. Have a look at column E, Total Value:
This total value takes into account the Attack, Defense, and Special effects of the card, and this value can now be compared to other values generated the same way. All other things being equal, card #2 is about twice as a good as card #1, and card #20 is about 6.5 times better than card #1.
When trying to figure out the cost of something, the scale becomes important. Lots of board games manage to keep the cost of something to a one- or two-digit number for the sake of easy math and lower cognitive load.
So let’s say I make the weakest card cost 1 and see where that takes me. To do that, I’d divide the total value of each card by… something… and get a cost. If I want 3.5 to be divided by something to get 1…?
Yeah, exactly. 3.5:
Hmm. That didn’t go as planned. It’s going to be really hard to ask someone to pay 6.428571429 of something…
Or did it?
In a spreadsheet, the easiest way to do this is to decrease the decimal places to automatically round those results:
Alright – this is better. You can round all the way down to 0 decimals if you just want to see the whole number.
One important note: just because this is the calculated cost doesn’t mean it has to become the cost that goes on the card. There’s some room for massaging the data here for any number of reasons. This is what I’ll be using column B for – the actual cost that goes on the card (in this example, at least) is separated from the mathematical formulas and calculations. Why?
Because the art of balancing means tweaking things to make fun.
Being perfectly balanced isn’t (usually) my goal. Fun is why we play. This is a pretty simple example, and your game might have many reasons to tilt the balance one way or another. Should a card feel more expensive (or cheaper) for any reason? This is where that can be factored in – but again, based on the data you’ve created.
To finish out the example, let’s manually type some numbers in column C based on the data thus far (compare the calculated cost in column D to the typed cost in column C):
Lots to look at here, I know. Balancing is an art, it’s a work-in-progress, and it’s rarely if ever perfect. It doesn’t replace playtesting, but spending some time on balancing can make a lot of other things fall into place.
One goal here is internal consistency, and this is where your judgement as a designer can override all the hard data / numbers that have been put into place. If people expect numerical patterns, they might wonder what’s going on if those patterns are interrupted.
In general, I’ve just rounded the numbers, with a few notes along the way:
- Card #4 has a cost of 5 – after seeing costs of 1, 2, and 3 on cards #1, #2, and #3, why the jump? It’s the triangular value behind the special effect, and an assumption that a level-4 special effect is that much better.
- Cards #5 and #6 have the same value and cost, but they’re derived in different ways. If these were both available at the same time, the player would get to choose between a card with a higher attack or a better special power. If these were weighted differently, they could end up having very different costs.
- Card #11 gives you a level-3 special effect (but pretty much nothing else) for only a cost of 2… Is that a fair price? Someone might just buy it for the special effect, knowing the other stuff on the card won’t help much. Will people still buy it? This is why we playtest.
- Card #20 is the very best card in the game, yet rounds to 6. Because it packs such a punch and may be very difficult to beat, I bumped the price up by 1. Anyone evaluating this card now has to ask whether it’s worth it to save up the 7 needed, or they might dismiss it for being too expensive.
Alright, so within this group of 20 cards, I’d be prepared to call this reasonably balanced and ready to playtest these assumptions. If the assumptions change, the weights might change, the values might change, and the costs might change. One playtest will reveal some cards as overpowered or underpowered, too cheap or too expensive, and so on. It’s all part of the cycle.
One more thing to note…
Look towards the right of the spreadsheet, columns N, O, and P:
Call this a force of habit. As part of balancing, I want to know at a glance whether the various amounts are being used the ‘correct’ number of times. What’s ‘correct’ may vary according to your interests – maybe you want a bell curve, or a long tail. Maybe you want each amount to be used the same amount of times. The ‘COUNT’ column is counted automatically using the COUNTIF function – tell the spreadsheet where to look (RANGE) and what you want to count (CRITERION). As you update the spreadsheet, this function counts matching things in the background.
I often add a ‘GOAL’ column just right of the ‘COUNT’ column, and the goal is just typed in. As I’m balancing, my sense of what the goal should be might change, but in general, I’m balancing towards that goal, or changing the goal to match the balance I’m happy with.
For more help…
I consult with game designers about any number of things. Spreadsheets happen to be one of them. If you’d like some help building your spreadsheet and balancing your game, let’s chat.
Over to you
What do you wish you knew about spreadsheets?