A while back I wrote an essay on invest-express game design, where I fully explain a lot of my process in designing virtual economies. When writing it, I initially thought it would be a 3 part series, but after writing part 1 and starting on the second, I realized that I didn’t provide a strong enough foundation, specifically on the topic of spreadsheet functions. This 2 part series will cover the most important spreadsheet functions that all game designers should know, with working examples that you can play around with to get the results.
In this first part, I’ll cover all the important basic math functions like SUM, COUNT, ROUND, AVERAGE, MIN, MAX, some string functions like CONCATENATE, HYPERLINK, and I’ll conclude with one of my most used functions, IF.
For anyone who has experience working with spreadsheets, most of this will seem trivial. In the second part, I’ll cover more complex stuff like VLOOKUP, FILTER, and SPLIT/JOIN.
Note: All of these functions will work with Microsoft Excel and Google Docs, though their syntax may vary slightly. All my examples will be done in google docs.
You can follow along with all the examples from the Sample Google Spreadsheet Here.
Our Sample Doc will use a set of Virtual Crops for a farming game, and compare them in order to see how they relate. These values are not tuned/balanced in any kind of way, so focus on the functions only!
Math Functions – These are essential for almost anything you do with numbers, such as economies, level/xp/energy curves, etc. Here’s what we’ll cover:
SUM is pretty straight forward; It adds up all the values in a range that you define, and then returns that total in the cel that calls the function. If we wanted to know the total cost of all of our crops, we would use:
This will add up all the values in the range we provided. In our sample doc, the total cost of all of our crops is 330.
AVERAGE has the same syntax as SUM, only it returns the average value in the range provided.
The Average Sell Value of all of our crops is 53.33.
MIN has the same syntax as SUM, only it returns the smallest value in the range provided.
The Min Sell Value of all of our crops is 22.
MAX is the opposite of MIN, it returns the largest value in the range provided.
The Max Maturity Time of all of our crops is 86,400. (24 Hours)
COUNT will tell us how many items are in a specific range.
The Count of all of our crops is 9.
ROUND takes a decimal value and rounds it to the nearest whole value. For this example, we’ll add a calculation of the Cost per Food for each crop (Cost / Food) and round it to the nearest whole value.
The Rounded Cost Per Food of Pumpkins is 4.
ROUNDDOWN takes a decimal value and rounds it down to the nearest whole value. For this example, 4.9 would ROUNDDOWN to 4. We’ve rounded down all the Cost Per Food Values in this example.
The Rounded Down Cost Per Food of Pumpkins is 3.
ROUNDUP takes a decimal value and rounds it up to the nearest whole value. For this example, 4.1 would ROUNDUP to 5. We’ve rounded up all the Cost Per Food Values in this example.
The Rounded UP Cost Per Food of Pumpkins is 4.
MROUND takes a value and rounds it up to the nearest multiple. If you want nice consistent numbers for a formula, consider using this. I like using this when wanting a cost to end in 5 or 0. We’ve rounded all the Cost Values in this example to the nearest 5.
=MROUND(D11,5) – D11 is our value we want to round. 5 is the multiple we want it rounded to.
The New Cost (rounded to the nearest multiple of 5) for Eggplants is 35.
Text Functions – These are essential for almost anything you do with text and strings. It’s useful for a wide array of things. Here’s what we’ll cover:
Concatenate takes a combination of cells and text to create a single string of data. It’s really helpful when you want to combine text with data that changes dynamically and that you don’t want to rewrite. Anything that isn’t a cel reference must be in quotes, and every entry must be separated by a comma.
=CONCATENATE(“The “,A7,” crop costs “,C7,” coins.”)
The Description for Strawberries is: “The Strawberry crop costs 20 coins.”
Hyperlink allows us to link websites from a cel. This is great if you have a subset of information but want to link to a wiki or reference site for more information.
The item image for Strawberries is displayed a hyperlink for http://www.farmgame.com/strawberry.png and text that says ‘strawberry.png‘.
Let’s take this a bit further, and make our hyperlink use some dynamic text based off the row we’re on. That way if we decide to change strawberries into grapes, we wont have to change text in multiple places.
We’ve broken the HYPERLINK paramaters into 2 separate CONCATENATE’s, and use the name of the item as part of the url, and as the display text. Our Dynamic Item Image for Strawberries is now a link to http://www.farmgame.com/Strawberry.png with the display text of ‘Strawberry.png‘
Logic Functions – These allow us to compare values and expressions, and display things based on the result of those comparisons. If you have any experience in basic scripting or coding, this will feel pretty familiar.
The IF function lets us display a value based on the result of a logical comparison. This might sound a bit confusing, but in plain english, it boils down to “If this is true, then display this other thing“. We’ll do a couple examples, so don’t worry if it doesn’t make immediate sense.
=IF(B7 < 5, “Yes”,”No”)
In this first example, we want to test if the crop unlocks at level 5 or less. Our logic test is ‘If the value in B7 is less than 5‘. If that’s true, then we display the text ‘Yes‘. If it’s not then we display ‘No‘.
If we want to check if a crop makes a profit more than 10 coins, we would use the following formula:
=IF((D11-C11) > 10,”Yes”,”No”)
In the logic check, we take the sell value and subtract the cost in order to determine the profit for a crop. This is done in ()’s to make sure we’re only comparing the result, and then we check if it’s greater than 10. If it is, we show the text ‘Yes’, and if not, ‘No’. In our example we’re checking the profit of Eggplants, which sell for 48 coins and costs 36. This gives us a profit of 12, which passes our logic test, and show’s a ‘Yes‘.
Hopefully this gives you a bit of insight into the power of spreadsheet functions, and enough knowledge to go out and start using them in your own work. Feel free to leave a comment if you want more examples on any of the functions I used here in this article, or if there’s other functions you want me to cover in the next part. In my essay series on designing an invest-express economy, I’ll be taking these concepts and putting them into a real life economy for a virtual farming game.