I’m knee-deep in Crematoria Online book two at the moment, and one of the main concerns I had was making sure that the progression of statistics was consistent, meaningful, and fun. Making them foolproof on the author’s part was also important, because he is an accomplished fool.
So I put my Excel-fu to good use and made a spreadsheet.
I’m sharing my learnings, because I feel like the math/stats aspect of writing GameLit/LitRPG is not something narrative-focused writers come to easily. So this might help.
You can automate your statistics in a way that makes sure your characters will always be getting level-appropriate gear. It’s as easy as putting your character’s level into a cell, choosing the rarity, and boom – your spreadsheet will pop out appropriate stats for that piece of gear.
The best part? Once you’ve set this up, you can use it for your entire series. I wish I’d done this before publishing Rise of the Crimson Order!
First up, you need to esatblish three elements in a single worksheet:
A table of levels and default stat growth
For thes above dummy stats (none of these are what I used for Crematoria Online), I chose an arbitrary formula: Primary Stat = Level*2.5. You can choose absolutely any formula that fits with your intended power curve.
Next, you need a table of gear rarities and the effects those rarities have on the stats of the items
Once again, these values are arbitrary. Choose your own values that will work for your world.
Lastly, you need a table of equipment slots and their gear weightings
A piece of chest armor won’t have the same stats as a pair of gloves, or a necklace, or a pair of boots. These stat weightings are designed to give some variance in the actual stats on the items, so they feel more appropriate.
Once you’ve set all of these tables up with your own data, now you can create your gear generator. You could do it on the same worksheet as your stat tables, but I like to keep them separate. It’s much cleaner.
Here’s where the fun starts.
You’re going to need to harness the power of VLookup to get this working. Once you get the hang of it, you’ll realise just how powerful Microsoft Excel is even without getting into the realm of macros. Here’s the Microsoft page on the function, but there are heaps of resources out there that will help you learn it.
There are four main elements to your gear generator that you need to understand before proceeding:
- Blue – These cells require Data Validation. Set these up as a drop-down list, so that values from your table of gear rarities and table of equipment slots are selectable.
- Grey – this is free text, but you’ll only be putting your numeric level numbers in here
- Yellow – VLookup. These cells are there to grab the modifiers and stat weightings you’ve put into your raw data tables so you can use them in formulas here.
- Green – formulas. This is the nuts and bolts of how your stat score will be generated. Without the above three types of cells set up, the formulas won’t work.
The VLookup can get a little tricky, so I’ll break down what I’ve done. For the yellow cell next to Weighting I want my spreadsheet to automatically change the weighting whenever I choose an equipment slot.
This is the formula: =VLOOKUP(C2, Ref!N2:O14, 2, 0)
Double dutch, right?
Let me break it down.
- C2 is our reference cell. This is what the VLookup function looks at to determine what other value is should display. In this case, it’s looking at the cell containing Head.
- Ref!N2:O14 is an absolute reference to a table selection. It encompasses two columns – N & O, and continues from row 2 to row 14.
- 2 refers to which column in the defined table we want our VLookup to display once it finds the value in C2. The cell values such as Head, Chest etc are column 1. Our stat weightings are column 2, and the weighting is what we want to display.
- 0 refers to whether we want an approximate match, or an exact match. We want to be exact, so we choose 0 = FALSE.
For the modifier VLookups, I have directed my spreadsheet to look at the cell next to Rarity instead of the gear slot cell. You should be able to set this up yourself now.
If you get those working, your spreadsheet is going to sing.
Once your drop-down lists have been set up, and your VLookups are working, you can now start on the formulas to calculate your gear attributes.
=((LEVEL*2.5)*MODIFIER)*WEIGHTING
The 2.5 in the above formula matches the *2.5 I used in my original table of levels and primary stat calculations. If your stats grow at different rates (eg, health grows faster or has a more complex calculation behind it) then you’ll need to alter this final formula to suit.
These calculations are subjective, and the ones I’ve used in these examples are just examples. Play around a bit and make sure your math matches your story!
So now that it’s all done, here’s a couple of examples of the output.
A pair of max-level Rare legs
A pair of max-level Epic legs
Once the gear is generated, I play around with the stats a little before putting them in the book.
Placeholder Rare Legs
Requires Level 20
Armor: 60
Stamina: 54
Strength: 45
Block: 28
Thorns: 15
or
Placeholder Epic Legs
Requires Level 20
Armor: 75
Stamina: 57
Strength: 54
Block: 36
Thorns: 19
As you can see there, the stats don’t exactly match the output of the spreadsheet, and I’ve done this intentionally. There’s always a level of randomness to stat generation in most games, and I like to make sure there’s an element of variance in my books too.
So there you have it. Go forth and create your own power curve spreadsheets if you like, share it around, and let’s demystify this element of GameLit/LitRPG writing!
Cheers,
Matthew
Matthew J. Barbeler 