Advanced Spreadsheet Skills for Game Designers Cary Walkin

Advanced Spreadsheet Skills for Game Designers Cary Walkin, CPA, CA, MBA @Cary. Walkin cary@carywalkin. ca http: //www. carywalkin. ca © 2014 Cary Walkin CPA, CA, MBA Let’s be friends!

About Me • • CPA, CA, MBA Creator of Arena. Xlsm – RPG Made in MS Excel Game Designer – 6 published games, 4 in development Director of Operations at Xenophile Media • Game Consultant – Evaluation, Monetization, Training, Game Design, Financial Management, Production Management, and Business Advisory © 2014 Cary Walkin CPA, CA, MBA

Goals for Today • Learn at least 1 new Excel tool and how to apply it to Game Design • Become more confident with your ability to use spreadsheets to: – document your designs – communicate your designs – iterate quickly and easily on your designs © 2014 Cary Walkin CPA, CA, MBA

Looking Forward • • • The Value of Spreadsheets in Game Design Interactive Spreadsheet Design Theory Spreadsheet Communication Tips Prototyping with Spreadsheets Advanced Spreadsheet Tools Spreadsheets and Game Engines © 2014 Cary Walkin CPA, CA, MBA

Games Are Spreadsheets Product Layer UI/UX Layer Spreadsheet Layer • Marketing • Distribution • Programming • Art Design • Sound Design • QA © 2014 Cary Walkin CPA, CA, MBA • Game Design

Interactive Spreadsheet Design • Static vs. Dynamic Spreadsheets • Decision Variables • Formula Design for Interactive Spreadsheets © 2014 Cary Walkin CPA, CA, MBA

Spreadsheet Communication Tips • Just because you can understand it, doesn’t mean anyone else can. – – – Titles Labels Conditional Formatting © 2014 Cary Walkin CPA, CA, MBA

Spreadsheet Communication Tips • Interactive Spreadsheet Design and Spreadsheet Communication – Example Spreadsheet © 2014 Cary Walkin CPA, CA, MBA

Logic Functions and Conditions – IF • If a condition is true, result 1, else result 2 – AND • If all conditions are true, result 1, else result 2 – OR • If any conditions are true, result 1, else result 2 © 2014 Cary Walkin CPA, CA, MBA

Prototyping with Excel • Using Excel to prototype is a powerful tool to prototype systems to balance them quickly. • Game Design is iterative: Fail Faster. © 2014 Cary Walkin CPA, CA, MBA

Prototyping with Excel • Battle System Prototype – Example Spreadsheet © 2014 Cary Walkin CPA, CA, MBA

Naming Ranges 1. Drag select the range to be named. 2. Click in the Name Box to the left of the Formula Bar. 3. Type the Name such as Player_Level and hit Enter. © 2014 Cary Walkin CPA, CA, MBA

Data Validation (Dropdown Lists) 1. Select the cell where you want the drop down list. 2. Go to Data -> Data Validation 3. In the settings tab, under “Allow” select “List” 4. In the “Source” selection, put your range that you want to become your dropdown list. Ie. =Player_Level © 2014 Cary Walkin CPA, CA, MBA

Checkboxes • Enable the Developer Ribbon – Excel 2010 and 2013: File -> Options -> Customize Ribbon -> Developer – Excel 2007: File -> Options -> Popular -> Show Developer Tab in the Ribbon © 2014 Cary Walkin CPA, CA, MBA

Checkboxes 1. In the Developer Tab, Select Insert -> Form Controls -> Check box 2. Right click on the checkbox -> Format Control -> Cell link © 2014 Cary Walkin CPA, CA, MBA

VLOOKUP • The VLOOKUP function finds a value in a table and returns a value in a corresponding column. • Syntax: =VLOOKUP(value we want to find, table we want to search, corresponding column number where we want the returning value, True/False – Match Type) • Note Match Type: 99% of the time, we want an exact match so the final parameter will be FALSE. • Note: The value we want to find must be in the LEFT column of the table for a VLOOKUP © 2014 Cary Walkin CPA, CA, MBA

INDEX and MATCH • Index: returns a value at a specific row in our range. • Syntax: =INDEX(range, row number) © 2014 Cary Walkin CPA, CA, MBA

INDEX and MATCH • MATCH function returns the ROW NUMBER in our range where we find a value. • Syntax: =MATCH(value we want to find, range we want to search, match type) • Just like with VLOOKUP, the match type will usually be FALSE because we want an exact match. © 2014 Cary Walkin CPA, CA, MBA

OFFSET • The OFFSET function returns a value in a range that is offset a number of rows and columns from another cell. • Syntax: = OFFSET(Range Reference, Rows away from Reference, Columns away from Reference) © 2014 Cary Walkin CPA, CA, MBA

Using Solver for Quick Balancing • Solver is exactly what it sounds like. It will solve your math problems for you. • To enable Solver, click on the Microsoft Office Button -> Excel Options -> Add-Ins. Then in the Manage box, select Excel Add-Ins. Click GO. Select the Solver Add-in Check box. © 2014 Cary Walkin CPA, CA, MBA

Using Solver for Quick Balancing • Scenario: We have received data that our game is too hard. We decided that in order to balance our game, the enemy HP should equal the player HP at level 10. Recalculate the enemy HP curve. © 2014 Cary Walkin CPA, CA, MBA

Linear Programming for Balancing • Scenario: We want to change the experience scale so that the experience curve is between 100 -1000 xp. • Decision Variables • Constraints © 2014 Cary Walkin CPA, CA, MBA

VBA, Macros, and Simulations © 2014 Cary Walkin CPA, CA, MBA

VBA, Macros, and Simulations © 2014 Cary Walkin CPA, CA, MBA

VBA, Macros, and Simulations © 2014 Cary Walkin CPA, CA, MBA

VBA, Macros, and Simulations © 2014 Cary Walkin CPA, CA, MBA

Spreadsheets and Game Engines • Segregation of Duties • Talk to your programmer, know what they need. – Integer Data: 3, 6, 23 – String – Text Data: “All your base are belong to us” – Bool – 1/0 Data: TRUE, FALSE © 2014 Cary Walkin CPA, CA, MBA

Spreadsheets and Game Engines • More advanced: Depending on the type of game, you can even do level design in a spreadsheet where the spreadsheet dictates an array of prefabs that comprises your level. • PRO-TIP: Print out a visual reference guide for the prefab ID codes. © 2014 Cary Walkin CPA, CA, MBA

Goals for Today • Learn at least 1 new Excel tool and how to apply it to Game Design • Become more confident with your ability to use spreadsheets to: – document your designs – communicate your designs – iterate quickly and easily on your designs © 2014 Cary Walkin CPA, CA, MBA

Thank You! Cary Walkin, CPA, CA, MBA @Cary. Walkin cary@carywalkin. ca http: //www. carywalkin. ca © 2014 Cary Walkin CPA, CA, MBA Questions?