This is the second post in a series of articles about learning the great functions in spreadsheet tools like Microsoft Excel and Google Spreadsheets. You can read Part 1 here.

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 be similar to the one used in Part 1, but we’ll be building things to be a bit more dynamic. Values will start to use lookup tables, and more advanced functions that will be covered in this doc. Our Virtual Crops will start to reflect the values needed for any kind of invest/express type game, which should segue nicely into the next series I’d like to cover on economy design. Please keep in mind that these values are not tuned/balanced in any kind of way, so focus on the functions only! The next series of posts will cover building actual tuned values.

Here’s what we’re going to cover in this post:

  • Data Validation
  • Vlookup()

I had originally planned to cover a bunch of other functions, but after writing out the first 2, this post was already becoming a monster in size. Looks like a Part 3 of this series is definitely going to happen. After speaking with other people who have read this, it’s likely it’ll extend to a Part 4.

Data Validation is not exactly a function, but is definitely something worth knowing. It allows you to set specific rules and criteria for the kind of data that can appear in a cell, and also lets you create drop down lists of data. These are great for ensuring that data added to your spreadsheets adhere to specific rules. If someone takes over maintaining your spreadsheet in the future, they’ll have a much easier time if your tuning and information data is easy to work with.

In our example sheet, we’re going to create a list of maturity times that we’ll use to populate into a dropdown list in a cell. Trust me when I say this sounds more complicated than it really is. I’ve created a sheet called ‘Lookup Tables’ which we’ll use for the reference data.

Here’s the first lookup table I’m adding. It’s a list of literal times in english text (which we’ll populate in a dropdown list) and their actual time in seconds, which we’ll use a vlookup to populate in the next section.

Screen Shot 2013-01-15 at 10.05.36 AM

So lets get this into a nice looking dropdown list!

In our ‘Crops’ sheet, I’ve created a new column called ‘Maturity Time’, which is where we’ll make it easy to determine the value for a specific crop. Right click the empty maturity time cell for Strawberries (B7) and select Data Validation from the bottom.

Screen Shot 2013-01-15 at 10.21.57 AM

Our Cell Range will remain the same (Crops!B7), but it’s the criteria we’re going to modify. Click on the ‘number’ list and select ‘Items from a list’. From here you can put in a comma separated list, and more importantly, you can define a range of cells to use, which is what we’re going to do.

Screen Shot 2013-01-15 at 11.33.09 PM

Click the select data range button. Once you get the ‘What Data?’ popup, click on the ‘Lookup Tables’ tab. Select the full range of cells with literal time values. You should see the following value in the field:

‘Lookup Tables’!A5:A27

Click OK, then click save. We now have a dropdown list!

Screen Shot 2013-01-15 at 11.59.28 PM

Be sure to click the drop down box arrow to pick the values you want.

Screen Shot 2013-01-16 at 12.01.51 AMTIP: If you want to use this dropdown list for the rest of the cells, just use the format painter (the icon to the left) to copy the format to the rest of the crops.

So, we’ve got cool dropdowns populating now, but if our database only takes values in seconds, we’re going to need to populate a field that it can read. Luckily for us, we have a function called Vlookup that can do this for us!

I’ll be the first to admit that until I really had some experience working with this function, I didn’t really understand it. Hopefully I can do an acceptable job of explaining why you will want to know it, and how to wield its power!

What Vlookup will allow us to do, is automatically populate a field, based on the value of another field.

Meaning, when we pick a maturity time from the dropdown list we just created, the maturity time in second’s field will automatically populate with the value in seconds. In order to do this, it will look up the value of the maturity time on the ‘Lookup Tables’ sheet, and then pull the value of the corresponding Cell next to it.

So in the case of our Strawberry, when we pick 5 Minutes as our Maturity Time:

Screen Shot 2013-01-16 at 12.25.10 AM

The Maturity Time in Seconds field is automatically populated with the exact value of 300 seconds. It does this, because of the table that we bult on the ‘Lookup Tables’ sheet, and using a Vlookup.

Still with me? Good. Let’s look at the syntax for the function:

VLOOKUP(search_criterion, array, index, sort_order)

A bit scary right? It’s ok, let’s break down each value and build the vlookup for strawberry maturity time:

The search_criterion is just the value we want to lookup somewhere else. In this case, it’s the literal maturity time that is on the crop sheet. We’ll search for this value on the ‘Lookup Table’ sheet.

Currently our function looks like this: “=VLOOKUP(B7,” – It’s going to search somewhere for the same value as what is in the cell B7.

The array is the range of the cells that contain both the value we’re searching for, and the lookup value we want to return. If there are multiple columns between the column that will contain the search criteria, and the lookup value we want to return, specify the full range of columns in the range.

Our function now looks like this: “=VLOOKUP(B7,’Lookup Tables’!A:B - It’s going to look for the value of B7 on the ‘Lookup Tables’ sheet, in columns A and B.

You can type these values in by hand, or click them individually while separating them by a comma from google docs.

The index is the column number in the range we provided that contains the value we want to return. All ranges specified start at 1, and increase. So in our example, Column A is 1 and Column B is 2. We want to return the value of Column B (300).

Our function now looks like this: “=VLOOKUP(B7,’Lookup Tables’!A:B,2 - It’s going to look for the value of B7 on the ‘Lookup Tables’ sheet, in columns A and B, and then return the corresponding value of the cell next to the match. So whatever is in the cell to the right of ‘5 Minutes’ will be returned.

The sort_order paramater is only necessary if you’re trying to find the closest match to the value you provided, specifically in a numerically sorted list. Because we know the match will be exact, we don’t need to worry about this. Our sort_order will just be set to false.

Our Complete function now looks like this:

=VLOOKUP(B7,’Lookup Tables’!A:B,2,false)

If we copy this formula to the rest of the blank maturity time (s) values, and put in appropriate times for the literal values in Column B, you’ll see that their maturity times populate with the proper lookup data!

Screen Shot 2013-01-16 at 12.51.01 AM



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!

Screen Shot 2013-01-06 at 4.36.26 AM

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()
  • Average()
  • Min()
  • Max()
  • Count()
  • Round()
  • RoundDown()
  • RoundUp()
  • Mround()

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()
  • Hyperlink()

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 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 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.

  • IF()

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.

As part of my new blogging goals for the year, I hope to have 3 unique types of posts for you all to enjoy. The first are essays, which I share my thoughts about various topics, and my learnings in the industry. The second is interviews with kick ass people. And today marks the first of the third type; Stuff That Mattered. This is where I share a list of all the things I’ve consumed this week that I feel are worth sharing. Let’s begin!


 Minimalism: Live a Meaningful Life  A great read so far that focused around the concept of minimalism (duh). More than just living a life with less stuff, it focuses around improving your life in 5 major categories; Health, Relationships, Passions, Growth, and Contribution. Also, if you have a Kindle device, you can borrow the book from the Kindle library for free!



 8 Minute Meditation: Quiet Your Mind. Change Your Life - I never considered myself to be the type of person who would find a book on meditation to be helpful, but after a recommendation on a blog I follow, I made the choice to check this out and haven’t regretted it at all. This book focuses on developing a simple 8 minute meditation routine that can help reduce stress, make sleeping easier and more fulfilling, and improve your overall mood. I can’t say for sure if it’s had a major impact for me, but if you’re finding yourself stressed, it might be worth checking out.



  • The Story Behind the 47 Percent Video – An amazing recount of the story behind one of the biggest political controversies in recent history. A must read.
  • Blizzard Game Director Dustin Browder Explains Why StarCraft II Pro Gamers Are Athletes – A great interview with the Design Director on Starcraft 2, a popular PC game that is more than just a game, it’s what’s referred to as an ‘eSport’. The eSports scene is a cultural phenomenon in countries like South Korea, and its spreading across the world. The people that play these games train for 12-14 hours a day in order to compete at the highest level.
  • Cayenne – Turn Up the Heat! – An interesting article that promotes the benefits of Cayenne for a wide assortment of benefits. While the article is definitely lacking in scientific backing, it definitely makes me question the inherit benefits of something that has been used for so long in holistic medicine.
  • Viktor Blom: The Man, The Myth, The Legend – Legendary Poker Player Phil Galfond shares the story of Viktor Blom, widely known in the professional poker scene as ‘Isildur1′, who rose up the ranks due to his incredibly aggressive playstyle, and overall love of the game. A great read even if you’re not really all that into professional poker.
  • What’s your secret that could literally ruin your life if it came out? [NSFW]- From the ‘Ask Reddit’ section of Reddit comes a dissuasion about the world shattering secrets that their users are only able to admit to millions of anonymous readers over the internet. Flagged as NSFW for the sake of the content. Consider yourself warned!


  • The Voice of Mario – Charles Martinet Interview – An interview with the voice of Mario, the most iconic character of all time. Learn the history of how he got the role, and hear some great voices from the wide cast of mario related characters that he does the voices for.

  • Valve’s Secret 2013 Virtual Reality Plans? – Internet gaming celebrity Athene speculates on some possible virtual reality/augmented reality hardware/software that Valve may be developing. If you’re into futurist ideas and thinking about what might be possible in the future, this is definitely worth checking out.

[click to continue…]

As part of my commitment to writing on a more consistent basis on this blog, I’m going to start interviewing friends and colleagues in the video game industry, and anyone else who I find interesting, to share their knowledge and how it relates to the things that matter to me. The first of this series of interviews is with Elizabeth Sampat, an incredibly talented game designer and writer, who also happens to sit 5 feet away from me at work. She was kind enough to sit down with me the other day and share her thoughts and experiences on non-digital game design, the ‘game remix’ scene (which I had never even knew existed) and a bit about her experiences with kickstarter. Some of the other cool topics we talked about will be used for future articles and essays, so look forward to those in the coming weeks.

You should also follow Elizabeth on Twitter

So Let’s Begin! Anything in () are just my inline comments.

MHz: So what are you doing these days, aside from the stuff at work that we’re not able to talk about?
ES: I’m finishing up distribution for They Became Flesh which was my last kickstarter. It’s my most recent tabletop RPG. I’m also looking at the 2013 con season, and figuring out  where I’m going to go, and what I’m going to talk about.
MHz: So do you just get a booth? Or were you invited to talk at some events?
ES: It really depends on the convention. I usually get a booth at Gencon though.
MHz: And you sell all your games at your booth? You have like 3 published games now, right?
ES: Yes, yes.
MHz: See, I looked this stuff up online, I did some research to prepare :)
ES: Ooh, you’re prepared. *laughs* Yea I have 3 physical games, and one that’s a PDF only. (Note: Buy them all here!) It’s just a $5 download that changes the rules to Clue.
MHz: Wow, that’s interesting. It’s like a mod-pack.
ES: Yea, so instead of the regular rules, you use the weapons to kill everyone, and after 45 minutes you have to have 2 people left, and find the duffle bag with 50 million dollars in it.
MHz: Damn that’s awesome. I’ve never heard of anyone modding existing game’s and selling modified rulesets.
MHz: If you don’t mind me asking, does something like that sell?
ES: Yea, it’s listed in a few places, like The Unstore  which is basically just a hub for individual distribution of indie games. I also sell it on my blog. Murderland, as it’s called, is probably the most money per hour  I’ve ever made on a project, due to how long it took to make.
MHz: So does it sell on a consistent basis?
ES: It sells sporadically. It really depends on if people are arguing about my games on the internet, or like after the #onereasonwhy thing happened on twitter (Look for more discussion about that in a later article), or there’s just a general spike in people knowing who I am, then my stuff sells. Funny enough, even when people are complaining about you on the internet, you stuff sells. It’s like they want to find out about you so they know what they’re talking about.
MHz: So is this whole ‘mod’ thing common? Do other people do it? (Gimmie some examples!)
ES: Yea! Though a lot of people don’t charge for it. I guess I’m just an ass like that. (Not at all!) Especially in the indie rpg community, there’s a lot of ‘hacking’. For example, I made this game called Blowback, which is like my love letter to the show Burn Notice, which is one of my favorite TV shows, that I know you hate. (Yea, I hate that show, but that’s ok) So after the movie Inception came out, somebody hacked it, and came up with alternate rules and flavor so you could use it for Inception.
ES: My friend Liam, wrote a game about colonialism called Dog Eat Dog, and I helped him with the kickstarter. One of the stretch rewards was me posting a hack called “May the odds be ever in your favor” which of course is a Hunger Games hack, which works really well for this game.
ES: Probably the best example of all of this, is a hack for an RPG called Apocalypse World by Vincent Baker, It’s one of my favorite games. My friends hacked it, and turned it into a game for doing old school dungeon crawls, called it Dungeon World, launched a Kickstarter for it, and generated almost $100,000 from it.
MHz: Wow, that’s amazing.
ES: Yea, they worked on it for a couple of years, so it’s a pretty thorough hack. There’s actually this very rich history of game remixing in the tabletop game world.
MHz: I had no idea!
ES: Yea, so if you’re wanting to get your feet wet in that kind of game design, it’s fun to do that as a practice.
MHz: That actually gives me  a perfect idea for something exactly like that, that I’ve wanted to do. I love a lot of traditional family board games, but often feel like they’re a bit watered down. I love the idea of taking one of them and making an ‘adult’ version of them. A lot of them really need a more modern version that doesn’t reflect the era in which they were created, which is usually like the 50’s or 60’s. So I’d love to do a version of one of those games that covers more of the things people experience in a modern tech driven world.
ES: That sounds very interesting!

Funny enough, this will likely be my first non-digital project I work on this year. 

Well, that’s it for this part of the interview. Check back for more of this awesome talk in some later posts.

This is a repost of an essay I wrote for the game dev group altdevblogaday last year. I wrote this after working on CityVille, and a few other small projects. I’ve updated it to reflect things I’ve learned since writing this. Most of these steps can be applied to mobile games as well, with the exception of the 4th tip. On mobile, the player experience is significantly condensed, so that anything more than asking a user to connect to facebook from your game is likely to result in drop-off.

Disclaimer: The following are my own opinions, and observations, and do not represent those of my employer, either past or present. Nothing in this document represents confidential or privileged information. Use this information at your own risk.

After spending my first 2 years working in social games, I’ve had a chance to play through a very large number of social games, from the biggest games on the platform, to many smaller ones. One thing that I’ve noticed across a large number of these games, is their ability to borrow mechanics, viral flows, concepts, systems, and a large number of other things, and somehow manage to fail to properly implement a great FTUE. The FTUE is arguably (in my opinion) the most important part of the game that you will build. It’s the only thing, aside from your loading screen that all installed players will see. The number of players who make it through your tutorial, the first session, and if you’re lucky, session 2, session 3, and so on, will determine the ability for your app to “hold water”. As those players retain, they should be given the right opportunities to invite their friends to the game to play with them.

I wanted to share 6 observations/tips/lessons that will hopefully help fellow social game developers in creating successful FTUE’s.

Design, Test, and Iterate; Early, and Often!
The second you have your core loop implemented in your game, you should start designing and testing your first time user experience. This will not be something you just do once and are done. (And if you do, you’re doing it wrong!) Play through it, have everyone on your team play through it, and have everyone submit notes and feedback. Isolate the good feedback, update your tutorial, and test again! Keep doing this until the very day you launch. Hell, after you’ve launched you should keep tweaking and optimizing your FTUE.

Limit the Number of Steps
While it’s important to guide your players through the first steps of your game, teaching them the very basics, it’s also important to limit the length of your guided tutorial. Empower your players to use the mechanics and methods you teach them, and never pull control from them for too long. Look at the successful games on the platform that have a guided tutorial, and look at how many steps they have. The best games are able to capture the core loop in a handful of steps, and transition out of ‘tutorial mode’ quickly. The player should have enough knowledge by now to complete basic tasks and objectives (like quests) until you start to unfold the subsequent mechanics and systems of your game.

Don’t Expose Too Many Features
As the space has evolved over the last couple of years, the number of features, systems, and mechanics included in each game has risen significantly since the early days of games like Parking Wars, and Mouse Hunt. New games have complex economic systems, character customization, and a wide range of other features that seem simple, but together can easily overwhelm an average player. That’s why you should limit what features are exposed to players. If you have multiple resource types, then front load the player with the secondary resources, and teach them how to make the primary resource. Once they get that down, then introduce them how to earn the secondary resources. Focus on the features that are the most eye catching, enjoyable, and fun to do, and delay the rest of the supporting features until later sessions. Players wont feel overwhelmed, and they’ll be more likely to pick up the complexity of your game if they are served it in small digestible bites.

Present Enough Viral Opportunities
This is part of that prior point about your app needing to ‘hold water’. In addition to being able to retain your players who install your app, those same players need to also become your means to reach new players! The way to achieve this is by presenting enough viral opportunities. A great example of this, is looking at the FTUE deconstruction of CityVille done by Kevin Rose. In the first 3 levels of the game (less than the first session lasts) the player is asked to invite friends, send gifts, and post to their wall a total of 9 times! It’s extremely important to note that while this may seem like a lot of opportunities to ‘spam your friends’.. almost all of these scenarios feel like natural opportunities to share information with your friends. Rarely do any of them feel ‘Forced’ upon the player in any way. It’s critical that if you do implement these viral opportunities that you make them feel genuine, and inviting for the player, or they’ll feel pressured and never want to share their experience with friends.

Measure and Track Your Results
It should really go without saying, but unless you have hard numbers to back it up, your instincts on how successful your FTUE really is, are likely wrong. Measuring your install funnel and identifying what % of players make it through each step/quest in your first session is extremely critical. If you have hooks established for every single guided click action, you can quickly determine where your sticking points are for your new players, and how effective you are when making adjustments. If you’re tracking all your quest start and quest completion points, you’ll also be able to tell what quests players are getting stuck on. If you’re tracking each quest task, you’ll know what tasks are too hard/complicated etc.. Put significant efforts towards tuning, modifying, and maximizing your FTUE conversion (players who become regular players) and you will stand the best chance at retaining players and growing your application. If your tutorial completion is not in the 80-90% range for organic users (people who find you naturally through searching) then you need to optimize. If you have dedicated product managers, they should be measuring your daily conversion rates for new users, their Day 1/Day 3/Day 7 retention, and their K factor (how many installs each new user generates).

NEW Optimize For Permissions First
With the more recent chances that facebook has made, developers are now able to present a tutorial and gameplay without actually forcing the user to give any permissions. This means that all the prior tips should be applied in 2 phases: Converting new users into ‘installed’ users, and then converting installed users into returning users. Your goal in the first phase is still essentially the same, only you should measure success by the % of your users that agree to install and give necessary permissions. Users who agree to give your game permissions are trusting you to not abuse them, so make sure you only ask for what you need, and you never break that trust.

Hopefully these tips will be of some use to those of you working in this exciting space. Please feel free to follow me on Twitter (@mzamara) or leave a comment if you like what you’ve read!