To some people, that word is scary---OMG NUMBERS EW, while to others it's exciting---I'm gonna figure out the BEST way to set up/build my champion, and then beat you because my build is better!
For me, theorycrafting is definitely the latter. I love seeing what the numbers tell me and then applying that in my gameplay! The goal of this article is to get you to feel the same way---or help you theorycraft more efficiently if it's something you already do. :)
Note that this guide assumes you have access to Microsoft Excel 1010+, but you can do most of what I discuss with Google's spreadsheet tool.
Step 1. Come up with a good question.
That's it! Come up with a question that you answer, but make sure it's a good question. Examples of good questions: "Are hybrid pen marks or magic pen marks better on LeBlanc if I'm vs a Riven starting Doran's Shield? What about against an Ahri starting Doran's Ring?" or "On Annie, if I'm going to be poking with Q 3-4 times between Ws, should I max Q first or should I max W first? And what will the implications on my mana pool be?"
Examples of bad questions: "What's the best build order for Caitlyn?" or "What should my support runes be?"
Now, the last two questions are definitely good questions to ask. But they're bad theorycrafting questions---they're just too general! A good question needs to have a very specific way to answer it using numbers from the game.
Let's look at our "good" examples. In the first question, we're wondering, do we want MPen or HyPen marks on LeBlanc? That's half the information we need---our OPTIONS, and our champion's STATS that affect our options. However---it's not enough. Your champion's stats are affected by the enemy's stats as well, and so we need to know what the enemy is. Riven starting Doran's shield, for example, is going to be a lot harder to poke down with autoattacks than an Ahri starting Doran's Ring. If you're against a Doran's Shield, that's 8 damage per auto that you're losing, and Riven will also have higher base armor than Ahri. On the other hand, you're likely to get more autos per Q/W/E against a Riven than against an Ahri because Ahri is also ranged. So when looking at the total damage you do in one "rotation," you're going to want to include more autos vs a Riven than you would against an Ahri.
If that doesn't make sense, don't worry---there will be examples later on!
Now let's look at our second question, about Annie. This question is somewhat more simple than the first one, because we've provided even more information---there will be 3-4 Qs between Ws. Annie's Q gains less damage per level than her W does, but it's slightly longer range and on a shorter cooldown; also the fact that it's targeted makes it easier to land and then back away. So which spell would you want to scale if this is going to be your combo? We can easily extend it to 2 Qs between Ws or 1 Q between Ws (in fact, "How many Qs between Ws do I need to make it worth it to scale Q over W?" would be another good question to ask). However, Annie also doesn't have the freest of mana costs if she's spamming her spells. So we add the second part---how sustainable is this? Given the amount of regen that you have, how many rotations will you be able to get through?
Now let's talk a bit about the "bad" examples. "What's the best build order for Caitlyn?" Well, what are you trying to accomplish? Max DPS upon reaching 6k gold into your build against a tank that's built Sunfire and Spirit Visage and also has a Warden's Mail? Or a build that will help you take down towers as efficiently as possible only 4k gold in? Or a build that will minimize the time it takes for you to clear a wave of creeps? Or a build that's best for dueling the enemy ADC? These questions could have very different answers.
And finally, "What should my support runes be?" Well, what champion are you playing, first of all!? Runes for Lulu will be different from runes for Taric will be different from runes for Janna. And furthermore, runes can change a ton on the matchup and overall game flow---if I'm gonna be 2v1ing as Nami, I might do scaling mana regen quints, but if I'm in a 2v2 as Vayne-Nami vs Lucian-Leona, I probably want flat HP instead.
What you should notice, though, is that bad questions are made up of multiple good questions. That is, if you have a general question like "What should my support runes be?" then you can break it up into smaller, more specific questions, which you then answer separately.
For the rest of this article, I'm gonna be using the question, "What should my offense quints be on Sona?" This is split up into several sub-questions. Separate cases include vs Zyra (a stand-in for a ranged support) or vs Leona (a stand-in for a melee support); and different options for offense quints.
(If you want to skip straight to the result, check out the end of the charts section of my Sona guide here (not linking that section directly cos sometimes the numbers change).)
Step 2. Decide what stats you need in order to answer the question.
In general, if you're looking at an offensive thing (how much damage do I do to ----, how can I best do damage, etc.), you need to know your AP, your AD, your flat ArPen, your flat MPen, your percent ArPen, and your percent MPen. You also need to know your enemy's stats---armor and magic resist for the most part.
Of course, these stats will come from different sources: items, runes, masteries, base stats, and hypothetically auras that your team has, so you need to make sure you include all of these sources!
Here are the stats that I use for this particular question.
So I have Zyra Armor Base and Scaling; MR Base and Scaling; the same stats for Leona; and also rune (Yel/Bl) and mastery (M) armor and MR. I do NOT include items on Zyra or Leona, but if I wanted to then I could make two more boxes for each of them; if I wanted to include percent armor/MR increase that would be another thing I'd want to make a box for.
NEVER EVER EVER PUT A VALUE DIRECTLY INTO A FORMULA. ANY VALUE THAT YOU HAVE TO USE SHOULD GO INTO A CELL, AND THEN YOU REFERENCE THAT CELL IN YOUR FORMULA!!!
There are several reasons for this. First of all is error checking---you might just make a mistake when trying to make a calculation! It's a lot easier to trace mistakes back if there are no values directly in the formula.
And second, League of Legends is NOT a static game! Patches happen ALL THE TIME, and the spreadsheet that you just spent all day making might be completely irrelevant in as short a time as 2 weeks from now when all of your champion's base values are changed! You do NOT want to have to change everything just because of something so minor as an increase in base armor by 2 or a decrease in base AD by 1.5.
If your calculations say "+(47+3*$C3)" where C3 is champion level, and suddenly Sona gets a buff to have 53 AD at level 1, then we'd have to edit everything. However, if the calculation says "+($A$4+$A$6*$C3)" then all we have to change is the cell A4 that has Sona's base AD written in it and increase that number by 3.
If you get confused about seeing too many things like $X$8 in your formula and not being able to read it well, you can name your cell! Right click on the cell, and this menu will pop up:
Then, you can make a formula like this instead!
Step 3. Figure out what intermediate values you need, and calculate them.
You really don't want your formulas cluttered, first of all. In general, my rule-of-thumb is to avoid having more than 8 cells referenced by a particular formula; if I need to reference that many values, then I should be splitting up the calculation.
In general, you NEVER want to be adding up multiple pieces that contribute to a single stat within your final formula. What I mean by that is, you should never have something like
=(DAMAGE/(1+(AR_RUNE+AR_MASTERY+AR_ITEM+AR_BASE+LVL*AR_LVL)/100). Instead, you would want all of those sources of armor added up into a single cell and then the formula to be =DAMAGE/(1+AR_NET/100).
There are a few reasons for this.
First of all, it makes it easier to find mistakes! (This is always a reason.) Second, what if Riot did something SUPER EVIL like changing the order of percent pen vs flat pen again!?!!? You would have to go through your final formula in every single cell and change it! Ewwwwww....Whereas you can just change the armor formula itself if you do it this way.
And what if you just want to change your situation? Suppose suddenly you're curious about what if Leona has W active? Or if Renekton decided to take the % increase in armor/MR mastery?
THE OVERARCHING GOAL HERE IS TO MAKE EDITS AS EASY AS POSSIBLE AND AVOID EDITING YOUR "FINAL" FORMULA AS MUCH AS POSSIBLE.
Finally, sometimes the intermediate steps can give you interesting information. "Oh, I didn't realize that an increase by ---- in armor actually led to a ---- damage change at this point in the game." All of what you do here should help you to build your intuition of what numbers mean what. And so the more "useful" numbers you see the better.
Let's go back to our example. I actually have 2 sets of intermediate values.
First, I calculate net stats amor/MR/AD/AP/Flat and % Pen:
Why are all of those numbers there 3 times!? Well, because the next set of intermediate values I calculate are the PHYSICAL and MAGICAL parts of Sona's damage, SEPARATELY.
Let's look at the formulas I use.
First, Magic Mod:
And for reference, X8 = Enemy Armor:
And X9, Enemy Magic Resist:
See how this could be confusing if I didn't do intermediate calculations?
Now, below that entire chart of constants, I have the following cell that explains to me:
Magic Modifier = 1/(1+(MR*(1-%MPen/100)-FlatMPen)/100). Physical Modifier the analogous for physical damage.
So if I forget exactly what it is that I did (say 4 months from now), I have a note reminding me exactly what I've done.
(n.b. the magic modifier is what you multiply your magic damage by to get the amount of damage actually dealt to the enemy champion accounting for their MR; same thing for phys modifier only for physical damage.)
Ok and now let's look at the actual damages set of numbers.
As you can see, it's "just" a ton of different potential combinations of damages you might do. The standard trading combo is AA+Q+QC, but you might also get AAs in between Qs...and then because this is level 6, you also have to wonder about your ult, etc. etc.
Here are the formulas for a few of the cells:
It references the current chord damage (which is a weird enough scaling that I just enter it by hand each level), AP, the AP scaling on chord, and of course the magic modifier.
Autoattack, Phys Damage:
Just your AD * the physical modifier.
Summing things up in that column
And then Net is just the sum of the physical damage and the magic damage.
See!? Not too bad :)
Now, you might be wondering about my use of $....
Step 4. Make your formulas universal enough that you can autofill EVERYTHING!
This probably shouldn't be its own step, but whatever. This is the SINGLE MOST IMPORTANT THING TO DO. Well, one of them anyway. You need to be able to click on a cell and then drag it to autofill an entire region. And then one you've filled all of the unique things, copy-paste that section to autofill everything else.
If you don't know how autofill works, or static values with $, just google "excel autofill" (without the quotes) and read a tutorial.
I'll give you an example of what I autofilled in these sheets.
Here's the picture of the values section again:
Basically, I made it so that if anything changes, I need to edit AS FEW CELLS AS POSSIBLE.
I also made it take as little time to fill in the grid as possible.
For those of you not familiar with autofill (I'm not making an entire guide about it), keep in mind I'm using the same word to talk about two things --- first, the ability to copy a formula to other cells by clicking the corner of the starting cell and then dragging it along the region; and second, the ability to have other cells change automatically by having their values just be "=STARTINGCELL"
What $ does is it holds either the row or column or both constant when doing click-and-drag autofilling, which is equivalent to copy-paste. Cut-and-paste works differently; it basically renames itself to the new location, so precedents and dependents are not affected in any way.
Step 5. Decide how you want to present the final data, and fill in that formula.
In this example, all I care about are the net damages. So I'm gonna remake the chart with only net damages instead of the intermediary damages as well.
The formulas here are all of the form "=F9," i.e. they just reference the upper chart. Again these charts can be autofilled by just clicking and dragging, once across and then once vertically.
I also added a conditional format. The rule is simply:
You can use more complicated conditional formatting as well though. Here's the format I used for the cooldown charts:
You should attempt to do as much conditional formatting as possible! Formatting by hand is f*king obnoxious!
I want to stop for a moment and mention DECIDNG HOW TO BEST PRESENT DATA IS REALLY, REALLY, REALLY, REALLY DIFFICULT! IT TAKES A LOT OF PRACTICE TO GENERATE CHARTS THAT ACTUALLY CONVEY WHAT YOU WANT THEM TO CONVEY AND ARE EASY TO READ! However, it's also REALLY, REALLY IMPORTANT! If you do a whole bunch of calculations but you can't look at any of the numbers at the same time, it's gonna be really hard for you to actually learn anything from your calculations.
STEP 6. MAKE SURE EVERYTHING IS CORRECT!
Ok, maybe this is actually the most important thing!
I want to introduce you to a feature of Excel that you may or may not be familiar with: Dependent and Precedent tracing!
Here's how you do it: Find the Page Layout tab at the top, and then you'll see a section that looks like this:
This can help you a TON to catch mistakes made when potentially overusing autofill or forgetting a $ somewhere or putting a $ that you didn't mean.
And then the final thing you should do is always the check of "Does this make sense???" If it says that your Q is going to be dealing 70 damage at level 1 with 0 bonus AD or AP, you proooobably made a mistake since Q only deals 50 damage! Make sure you didn't accidentally put a - sign when you meant a + or something like that.
You can also edit your source cells to see if numbers change the way you expect them to. Let's give Zyra 100 armor. Now is the amount of damage we're doing to her slightly less than half of our stated damage (slightly less than to account for penetration that you might have).
Step 7. DUPLICATE YOUR CHART WITH OTHER STARTING VALUES.
This comes AFTER you've done all your error checking, AFTER you've done the pretty formatting. Absolutely dead last. Now you're ready to change your starting values and compare numbers.
Notice how in the stats section I have some orange cells and some purple cells? Yeah, those. The orange cells are cells that I can change the values of in order to adjust starting info---say suddenly Sona is speccing 21 into offense and has penetration, or I want to give her a Haunting Guise---or, more reasonably, I just want to see what happens with different options for offense quints. I can change those numbers and then look at the change in results of my final charts.
And then the purple cells are cells that I can change based on the champion level.
Step 8. MAKE GAMEPLAY DECISIONS BASED ON YOUR NUMBERS!
"Awww but I really like having AP reds on Sona..." Well we showed here that it's not actually good. So don't do it. If you're doing theorycrafting, the numbers aren't lying to you. Adjust your gameplay or rune/mastery choices based on what the numbers tell you!
That does come with one caveat---say you get more DPS from AD as opposed to AS, but you feel like you play better due to the faster AA animation. Sure, go for AS! That's something that's qualitative, and theorycrafting can only help you make quantitative decisions. Qualitative ones are still up to you!