# Opinion: Stop Being The Useless Designer - Excel and FormulasOpinion: Stop Being The Useless Designer - Excel and Formulas

In this reprinted <a href="http://altdevblogaday.com/">#altdevblogaday</a>-opinion piece, Jagex's Claire Blackshaw offers advice for being a useful designer, starting with the brick and mortar tool for designers: Microsoft Excel spreadsheets.

Claire Blackshaw, Blogger

August 19, 2011

[In this technical, reprinted #altdevblogaday-opinion piece, Jagex's Claire Blackshaw offers some advice for being a useful designer and learning "Hard Skills", starting with the brick and mortar tool for designers: Microsoft Excel spreadsheets.] Let's face it, there is nothing more annoying than being bossed about by someone who is useless.? So here are three simple rules.

• Work with them in the trenches.

• Everyone in the trenches has to be useful.

• Supplement, don't replace.?

So, acquire some "Hard Skills" fast and be useful. This is a multi-part post for some places to start developing those "Hard Skills".? Though I encourage you to jump into your own tunnels of exploration. I hope this is the first of a multi-part post focusing on various tools or hard skills for designers. Introducing a tool or skill, then getting you interested. THE BEST PLACE TO START IS YOUR IN-HOUSE TOOLS!!! Part 1: Excel & Formulas Excel is the brick and mortar tool for the designer, though really any decent spreadsheet software will do and have most the features I list, so pardon the Excel centric explanations. With Excel, you can build entire complex systems and mimic game systems, allowing you to not only prototype systems but also balance and analyze them. Formula Introduction Equations are the bread and butter for Excel. So here is a crash course introduction.

=10 + 5 + C2 – SUM(\$A\$1:D10)+Sheet2!A2

 = must be the first character to indicate it's a formula. 10 You can use "magic numbers" ? in your formulas, but DON'T! Unless it's super obvious you're better off using a cell reference to expose your logic. + 5 Most basic math can be exceled in excel formulas. We will leave calculus and statistical stuff aside for now ;-) + C2 This references cell C2, pulling the value from that cell. This is better than using constants or magic numbers. When you copy and paste a formula the program automatically adjusts all the cell references to their relative position. So if you copy this formula into the cell on its right C2 becomes D2. More help here SUM() This function returns the sum of one or more numbers. Excel has a whole range functions for use. Remember to use F1 and Google, also the auto-complete is pretty awesome. \$A\$1:D10 This is a range including all cells from A1 to D10 that's a 4×10 block of numbers. The \$ indicates an ANCHOR. So if you copy and paste this formula it won't change \$A or \$1. Sheet2!A2 This refers to a cell on a different worksheet.

Okay that's a fast crash course introduction into formulas. Need more help? Head over to Excel Introduction. Named Cells  Things without a name or context are extremely frustrating. Often a design workbook can become massive. So, if a cell is used in many places or referenced on different sheets, then NAME IT! It goes without saying your sheets should be named as well. This is the most common tip I give for Excel. Naming a Sheet: Double click on the sheet name – Further Guidance Naming a Cell: Click the cell name to the left of the formula bar.  - Further Guidance Naming a Range: Same as cell but selects a range. Random Numbers Random numbers are always needed.

=Rand()

Generates a random float between [0:1] from which all your calculations can flow. Gotcha / Quick Tip: The random number changes every time Excel does an evaluation pass which you can force by pressing F9. VLOOKUP Is Your Friend VLOOKUP allows you to find data matching your request in a range of data. It’s the backbone of many of my formulas. The most common use case for this I found is when I have data from a separate source and I want to perform a bunch of lookups to bring data together on another sheet to add meaning.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Using the lookup_value it finds the row in the table array then returns the Nth item in that row relative to the start of the table, based on col_index_num. range_loopup  which allows you to have fuzzy matches. One Gotcha is that the look_up value must be in the first column of the data, so sometimes you need to rejig you data a bit. Further Guidance The Power of Pivot Tables Ah, the best kept secret of Excel. You can either select a bunch of data already in Excel and go Insert -> Pivot Table or you can just import from a Data Source like SQL. Then you can group and reformat your data in ways which are so useful. Now I grabbed some sample data from contextures.com. It's a bunch of sales data for multiple stores. Here is a sample cut out.

 OrderDate Region Rep Item Units Unit Cost Total 1/6/10 Quebec Jones Pencil 95 1.99 189.05 1/23/10 Ontario Kivell Binder 50 19.99 999.50

I can then take this data and with Pivot Tables see the following in a few seconds. You can quickly pull out summaries and analysis on data with this tool. I strongly encourage you explore it further as entire books have been written on the topic. Feel the power of pivot ;-) Further Guidance Cell Formatting Humans like color, seeing a page of number means very little to us. So wherever possible get things into graphs or charts. Though sometimes that's not appropriate. In these cases, conditional cell formatting is your friend. It can quickly highlight highs / lows, relative values or just point out the bottom or top 10 percent for example. This kind of quick visual reference point will often highlight problem areas in your game systems. Quick Tip: Setting your numbers to the same color as the background preserves the data but brings forward the formatting icons, letting you shrink the cells. Further Guidance Bins and Frequency Using data bins is a common trick in data analysis. By breaking things into discrete chunks for analysis, you can speed up calculations and perform analyses which otherwise would be difficult or impossible. For instance, say you are looking at a bunch of kill data for players which has the exact position and time of death. By breaking the timeline into chunks (or bins) and turning the map into a grid (breaking x & y into bins) you have group the data into manageable chunks. First thing you will need to do is define your bin size and range. I recommend making a named cell for size (or step), minimum and maximum, then using relative formulas to make a range of bins. Lastly, select the cells which will have the result and use this formula.

=FREQUENCY(dataarray,binsarray)