To give an example where spreadsheets make sense for game balancing: I am currently developing an RPG. I wanted to design a formula for the experience needed to reach each character level. My goal was that each level takes a bit longer to reach than the previous one, even though the character is stronger with each level and thus makes more exp per minute.
So I created a spreadsheet with a row for each level and the columns "EXP to next" and "expected EXP per minute" which were a function of the level. The third row "minutes per level" was calculated from the previous two. That way I could easily see how much time the player will spend on each level with my exp per level formula.
My first formula used to calculate "EXP to next" in fact resulted in a decrease of the time spent on later levels, because I underestimated how much more exp high level characters will make. So I changed the formula from quadratic to exponential. That fixed it, but now the lower levels only took seconds. Adding a multiplication factor got nice numbers for the lower levels, but levels above 60 took several years to reach, which was rather overshooting my goal.
After a lot more fiddling around, I finally got a pretty nice formula which did what I wanted. Simulating all those formulas in a spreadsheet surely saved me weeks of playtesting.
Some random examples from other genres where using spreadsheets could makes sense:
- Side-scrolling shoot-em-up: Average lifetime of each enemy ship when the player concentrates its firepower on it. Important to see how many new enemies per second are still manageable.
- Real-time strategy: Simulating combat of each unit against every other one to see which one wins with how much health left. Check these results against the unit costs to get a hint which ones might be over- or underpriced.
- 4X: Model economical growth over time and research progress over time to find a fair cost for each new unit/facility which becomes available.