I use Excel, but there are other spreadsheet programs out there. OpenOffice’s Calc is a good free app alternative. That being said, I will be using Excel’s functions and while Calc’s may be similar, I cannot guarantee they are the same. We will build a spreadsheet together and in the process go over some useful functions and features and talk about game balancing.
Spreadsheets are useful because it provides a way for you to enter or create equations for things such as Health Points and then actually see the data at different levels. Another great thing is that Excel functions can use data from another cell. This means you can create several different data sets, one building off the other. This allows what I like to call Relational Formulas. These formulas stay consistent and balanced because as one factor changes, they also change.
I recently decided to see what I could do to improve my game’s current HP formula and rate of increase of weapon’s damage. What was happening was maximum HP was not increasing with each level as much as player’s stats and weapons at low to mid levels, but HP started to get very high at higher levels. This meant very short battles for level 5-30, but long battles for those 50 and up.
Lets build a basic HP formula sheet and I can show you how I balanced this. Open a new spreadsheet and name the first three columns: Level, HP and HP Diff., respectively. I usually make my headers bold just to stand out as well. Put a 1 in the first level column cell (A2).
Now in A3 type the following: =A2+1
There are a couple different ways of expanding a formula across a column or row. One way is to copy the single cell and highlight all the cells you wish to paste into and paste. Excel also allows you to hover over the bottom right corner of a cell and click, hold and drag over the cells you wish to put that function into. Keep in mind that wherever you move this equation, the cell it references will always stay relational. In this case, no matter what cell you copy this into, it will always add 1 to the cell directly above it. For the Level column, this is an easy way of not having to type 1-100 manually! If you ever wish to copy the value of a cell using an equation instead of the equation itself, just copy paste and then click the small box that appears nearby and select “Value Only.”
Lets click and drag the bottom right corner of A3 down A21. That will give us 20 levels to see.
Now for the HP formula. Most HP formulas involve level, that is why I included that in the first column. You could use this template to experiment with different formulas. If you have another factor in your equation, just add the data to another column and reference it in the formula. In my game I like to have each level’s new HP build upon their previous max HP since they may have increased their HP through Stat points. If I limit my equation so that each level has to have X many health points, it will not work well with any other means of increasing max HP.
After much experimenting I will use the following equation: Square root of your level * 1000 + your previous HP.
So enter into B2: =(sqrt(A2*1000))+168
That is just level 1 though, so enter into B3:=(sqrt(A2*1000))+B2
Why add the 168 you may ask? Well you can change that to whatever you want to move the starting HP. With 168 it gives us 200 as our starting HP, but you could add +968 and start at 1000 HP if you wanted. (If you are wondering where that equation came from, keep an eye out for a future post about how to create equations to do what you want. In this case, the square root allows me to have large increases in HP even at low levels, but still larger at higher levels, just not exponentially so.)
But what about the annoying decimal numbers because of the square root? This is where you will want to use the ROUNDUP or ROUNDDOWN function. Simply add it to the beginning of your equation and then add a coma and zero telling it to round to zero decimals. =ROUNDUP(sqrt(A2*1000)+B2,0) Putting 1 or 2 will make it round to 1 or 2 decimal points, respectively. Putting -1 or -2 would go to the opposite side of the decimal point and round to the tens and hundreds.
Now drag that equation down the column to row 21 and you will see the base maximum HP for up to level 20.
I am a big fan of seeing every possible variable I can and having them in data cells since I never know when I might want to use them in a formula.
The third column will simply show us the difference in HP between the HP for the previous level and the current for each level. The first row (C2) will be left blank. Type this into C3: =B3-B2
Drag it down to row 21 and you can see how the HP increases per a level. As mentioned before, you can now see how the HP jumps up a good amount just from level 1 to 2, but it continues to increase gradually, just not on a drastic curve.
The basic tips on making equations, dragging equations and rounding up or down can be used for anything, not just HP. You could use it to make a sheet that shows you the Experience points required for each level.
You could use this to list your weapons and their damage and then make an equation to make the cost of the weapon dependent on how much damage it does; now it is consistent and you don’t have to come up with numbers out of thin air.
Get creative and use this valuable tool to both document data as well as balance things by making them relational, or just experiment with different stat or cost values.