**Analyzing your data**

Now that we have some statistics that show how people are playing our game, we can start to figure out what we should fine-tune. The tuning goal for this game was 3,000 tokens per hour, with 2,500 coming from the game itself and 500 coming from the jackpot slot machine. The following formulas get us started:

Total game tokens earned (in B9): =sum(B2:B8)

Total jackpot tokens earned (in C9): =sum(C2:C8)

Total game length (in D9): =sum(D2:D8)

Instead of typing each formula by hand, only create the formula for game tokens (in B9, the cell below the current data). Then, select B9, C9, and D9, and use the Fill Right command (Control + R). You'll save yourself countless hours of typing if you learn and properly employ the Fill Right and Fill Down commands.

Now we can use the initial sums to calculate tokens per hour:

Game tokens per hour: =B9 / ( D9 / 3600) Result: 290

Jackpot tokens per hour: =C9 / ( D9 / 3600) Result: 82

It's important to remember the units of your data, which is why we must divide D9 by 3,600, since there are 3,600 seconds in an hour. Given those results, the game is paying out less than 400 tokens per hour, which is way off from our goal of 3,000 per hour. Of course, that analysis includes some really long games, which are skewing the results. Therefore, we can look more closely and exclude abnormally long games. To do so, I recommend the function sumif(), as follows:

Game tokens earned in games <30 minutes:=sumif(D2:D8, "<1800", B2:B8)

Jackpot tokens earned in games <30 minutes:=sumif(D2:D8, "<1800", C2:C8)

Total game length in games <30 minutes: =sumif(D2:D8, "<1800")

In this case, if you tried the Fill Right shortcut, Excel would properly change "B2:B8" to "C2:C8", but "D2:D8" would also change to "E2:E8", which would mess up the whole formula. Therefore, to tell Excel to keep the column "D" fixed even when using Fill Right, we add a dollar sign ($) in front of the D, like this:

Game tokens earned in games <30 minutes:=sumif($D2:$D8, "<1800", B2:B8)

With that formula, we could Fill Right and the formula would remain correct. Since this example only has a few columns, Fill Right doesn't matter too much since we could type three formulas by hand. When you're working with many columns of data, Fill Right becomes vital to avoid typing out tens or hundreds of slightly different formulas, when Excel could do it all for you with Control + R.

Now that we've eliminated the very long games, let's look at the results of the formulas to get our modified rate-of-tokens-per-hour: Game tokens come out to 1,543 per hour, and jackpot tokens come out to 580. We're still not up to 3,000 per hour, but we got a lot closer by intelligently updating our model of player behavior. To properly balance a game, you need accurate data and data analysis. One other thing to note with this example is that the sample size is very small and only contains seven entries. In a normal beta-testing situation, we would have hundreds or thousands of data points per day.

**String manipulation**

Although we tend to think of Excel as a tool for numeric data, I've also used Excel to manage text in many games. Recently, I was working on a vocabulary game where I had a dictionary of only singular words, and I needed plural words, too, so I created a table like the one in **Figure 2**.

**Figure 2: You can use Excel to generate a list of plural words, which is useful for word games.**

The data in Column A is taken directly from the dictionary file used in the game. The function in B2 is simply =right(A2, 4). Then I selected B2 through B9 and used the Fill Down command (Control + D). C2 contains =left(A2, len(A2)-4). The "-4" is in the formula because I want to split the word apart, leaving the last two letters (and quote and comma) separate.

Unfortunately, the rules for plural words in English are tricky. First, I started with something simple, like =LEFT(B2, 2)&"s", which simply appends an "s" to the end of the word. Then I realized I should detect words that end in "y," so I used an IF() statement, like this:

=IF(MID(B2,2,1)="y", LEFT(B2,1)&"ies", LEFT(B2,2)&"s")

After that, I realized I should actually append an "s" instead of "ies" if the letter preceding the "y" is a vowel. Therefore, I updated the formula to look like:

=IF(MID(B2,2,1)="y",

IF( OR(LEFT(B2,1)="o", LEFT(B2,1)="a", LEFT(B2,1)="u", LEFT(B2,1)="e", LEFT(B2,1)="i"),

LEFT(B2,2)&"s",

LEFT(B2,1)&"ies"),

LEFT(B2,2)&"s")

As you can see, the actual algorithm for finding the plural spelling of an English word gets complicated quite quickly. Nonetheless, if you build your formula one step at a time, you'll be able to create powerful results. Start small, and work your way up in complexity. At some point, you may reach diminishing returns for rare corner cases, and it's okay to handle those by hand. You can see the word "child" turns into "childs," even though the correct plural form is "children." I simply dealt with that case by hand.

These are only a few of the ways you can use Excel as a game design and data mining tool. You can also create probability and game simulations, advanced text processing, complex asset tracking combined with easy integration into game scripts, and much more. For anyone who wants to practice their Excel skills, I created several Excel puzzles that are available here: https://irafay.com/Excel. Enjoy!

*[Ira Fay is an assistant professor of game design at Quinnipiac University and is the founder of Fay Games, a development studio primarily focused on educational games. He was previously a senior game designer at Electronic Arts (Pogo.com).]*

Comments

comments powered by Disqus