Spreadsheet Theorycrafting

Posted: by Frostheim


Some very, very clever people have developed elaborate excel spreadsheets to model every aspect of hunter dps. There are several different ones (all of which give different results) but the most well-known and heavily reviewed is Shandra’s Spreadsheet, and it is a thing of beauty.

The way it works is you enter your gear, your gems, your enchants, your talents, your pet, your pet talents, your glyphs, your shot rotation, and all the buffs that you would have in a raid, and the spreadsheet calculates what your theoretical dps could be against a raid boss.

The spreadsheets tend to be particularly good at determining which gear is better than other gear. I think the most valuable use of the spreadsheets is determining EAP values.

However, these spreadsheets have given rise to an unfortunate beast I call Spreadsheet Theorycrafters.

Spreadsheet Theorycrafters

Basically what happens is someone sits down and changes talents (or glyphs, or rotations) around one by one on the spreadsheet and looks to see how each changes the dps result, and then figures the one with the highest dps number must be the highest dps talent build.

I’ve even seen hunter sites where this is the entire basis of their theorycrafting, testing, recommended builds, rotations… everything. They just post spreadsheet results. It’s so… it’s just unfathomable to me.

Needless to say, I don’t approve of spreadsheet theorycrafting.

The Problems

First of all, again, these spreadsheets are things of beauty and they are shockingly good at what they do, especially when it comes to gear or stat comparisons. They provide an excellent data point — but it’s still just one data point.

Here are some of the flaws with spreadsheet theorycrafting:

  • There are several different spreadsheets, and all give different results. Each one says it’s not perfect, but it’s the best. They can’t all be the most accurate.
  • Flaws are often found: the hunter community is constantly finding errors and these are constantly being corrected (at least in the best of the spreadsheets). The errors are found when someone does their own theorycrafting, or in-game testing, and finds some discrepancy with the complicated formulas in the spreadsheet.
  • They model a perfect world: all theorycrafting models some kind of ideal where you don’t make mistakes, or fights last a certain length, or you don’t have to run out of AOEs, or you never get stunned, you aren’t losing seconds and debuffs to switching targets, etc. The problem with just putting info into a spreadsheet is that since you aren’t doing the math, you aren’t necessarily aware of which elements will be strongly affected by real world situations (like the Chimera Shot glyph) and which will be only mildly affected.
  • They overweight some things: back when I was SV before the mana regen nerf, I would generally always be at full mana in 25-man raid boss fights. I regened more than I spent. However the spreadsheets all thought I would run out, and so they way overvalued Int, Spirit, and mana restoration stuff – meaning the spreadsheet said that mp5 would increase my dps, which was factually untrue.
  • They’re only as good as their input: I’ve gotten several emails now from people using spreadsheets and getting strange results. Turns out they had incorrectly entered some of the raid buffs, or duplicated a shot in their rotation. A single mistake in the data you enter can significantly skew all of your “theorycrafting” results.
  • Ultimately you need controlled, repeatable, and falsifiable in-game testing to prove results. And if that testing is substantially different from the spreadsheet results – well, I can assure you that it’s the spreadsheet that’s wrong. The damage that you do in-game is, in fact, the damage that you would do in-game. In other words, you need science!

Theorycrafting and Testing

In the world seen through Frostheim goggles, any kind of theorycrafting is useless without testing. But people don’t like testing because it’s a pain in the ass — it takes forever, and it’s super expensive. The problem is that most anything you’re looking at — a talent build, a glyph, etc actually changes your dps by far, far less than the difference in RNG (random number generator).

For example: I test out my shot rotation on the target dummy. I do 3,300 dps. I do it again, and I do 2,900 dps. Nothing has changed at all, that’s just RNG at work. So now I’m testing a glyph that theory tells me will gain me 30-50 dps. How will I know that an increase or decrease is the glyph, vs RNG?

The answer is that you have to fire your shot rotation over literally thousands of shots (ideally you’re using cheap vendor ammo for this). And that’s just to test one glyph, or one talent build. Then you do it all over again. As Party Girl says “You’re always in Ironforge. Are you at the target dummy again?”

How I Make DPS Choices

Here are the Frostheim steps to evaluating talents, glyphs abilities, etc:

  1. Sniff Test: first thing is just to look at stuff and determine which ones won’t make the cut. If something increases my health by 10%, I know that won’t have any impact on my dps. This is also the stage where I sit around for a while and try to think up clever ways to take advantage of abilities, or combinations of abilities.
  2. Paper Napkin Theorycraft: the next step is I do some crude and simple calculations to see approximately where things stand. If there was something that was on the fence on the sniff test, I’ll go ahead and eliminate it if it sucks at this stage. Mostly I’m determining what order to test in. This step is often done while driving.
  3. Collect Data: next step is a whole ton of target dummy testing to collect my baseline data for stuff like glyphs (dps totals without glyphs, percentage of damage from each shot, stats of each shot, etc.)
  4. Theorycrafting: Then I sit down and do the number crunching. As I’ve said before, the math here isn’t hard. The hard part is setting up your equations to take everything into account. The most common theorycrafting errors come from people who just set up their equations wrong so they double up on something, or leave something out. This is Data Point 1.
  5. Testing: Next is the really really painful part of actually testing in-game. I do testing on the target dummy, because it is the only perfectly controlled environment we have (assuming no one else is attacking it). I usually do this with raid buffs. This is Data Point 2.
  6. Spreadsheet Checking: I also plug the data into a spreadsheet and see what it has to say. This is Data Point 3.

Now I have three data points to compare. If they all agree, then it’s easy to smile and say my work is done; however, if one of them disagrees, then it’s time to go back and try to find out why one is wrong. I could have made an error in my Theorycrafting – it happens. The spreadsheet could be wrong – it happens a decent amount. The in-game data could actually be wrong too! Perhaps the presence of raid buffs would radically alter the result, rather than scale it across all options evenly. That also must be investigated.

The point is any one of these data points could be wrong, and you won’t know without more data points. And actually, let me stress something else here: if you raid ulduar and do 4k dps on XT, then change glyphs and do 4.9k dps the next week. That is not a data point! That is just a change, and you have zero way to know that the change is due to your glyph. You’ve now got RNG spread across 10-25 players and their buff/debuff procs, performance, etc. not to mention how often you have to run, switch targets, etc. If it’s not controlled and repeatable, it’s just not science.

I’m not trying to bash the spreadsheets here. They’re valuable, and especially valuable for comparing gear and generating EAP values. But they get less and less accurate the farther you stray from gear, and are useless at modeling a running fight.

So that is how our guides are made, for the most part. This is also why it takes me a while to get new ones up when a major hunter change happens. And this is also why I get annoyed as all heck when someone responds to a guide with something like “No that sux look at mai build I did 4.7kdps aimed glyph roks.”

Facebook Twitter Snailmail
  1. Zulf says:

    o.o Damn, Frost, your l33t. lol

  2. The spreadsheets aren’t meant to accurately predict your DPS, they are meant to help figure out whether something is an upgrade. Also, when you collect data on a boss dummy, you have none of the raid buffs and boss debuffs that determine the final output of a setup.

    For example- agility looks much worse unbuffed on a dummy than buffed on a raid boss. If you base your stat weighing on unbuffed dummy data, you’re going to see something like the mirror of truth being far better than the darkmoon card, however in a real fight, the darkmoon card is clearly superior. Best in slot, actually.

    Honestly, the scientific method without modeling help is ill equipped to handle the types of questions we use the spreadsheets to answer. It is used to make the modeling tools we build as good as they can be, but suggesting that every hunter go and gather their own data (small amounts from raid nights, or large amounts from dummies) is doing them a disservice.

  3. Frostheim says:

    Of course you can test on the target dummy with raid buffs! Sure it’s hard to get a bunch of people together to buff you just for testing — but it’s really easy to go over right after a raid when you’re still all buffed up.

    I’m not asking every hunter to collect data. I’m just saying that if you’re going to disagree with our data, you should have data of your own to counter with. And that spreadsheets are just wrong a decent percent of the time (often through no fault of the spreadsheets).

  4. I could never get my guild go to a targeting dummy- if we have time to do that, we have time for another attempt on whatever we’re working on.

    When you say that the spreadsheets are wrong a decent percent of the time, do you mean that they recommend an upgrade that’s actually a downgrade? Or do you mean that the data they give in terms of DPS for a setup is not accurate?

  5. Frostheim says:

    I mean that they could show a glyph (or talent) as better, when in the real world it’s worse. As I said, I think the spreadsheets are pretty good with gear comparison.

    When using spreadsheets, you’re looking at qualitative results — which dps is better or worse, because the exact numbers aren’t exact :) And by decent percentage, I’m talking like 30% or so.

  6. ieatkittens says:

    Thank god, the devil, Elune, or whatever else you people pray to, that Frost does all this work instead of most of us :) I’m way to lazy to do in-game testing, so your guides here are a life saver. And as Euripides mentioned, the real bonus of spreadsheets are to search for gear upgrades – which is the only sourse for gear upgrades I ever use anymore. I think combining these forces is about the best us hunters can really get. Frost, thanks for going into detail regarding your specific tactics for testing – as any true scientist that publishes articles knows, steps for results must be clearly laid out in order for their possible replication!

  7. @Frost Yeah, the “dps” numbers are really more like imaginary DPS numbers. I find that it’s proportional to my real world DPS on patchwerk type fights, but about 30% higher. Still, the data is really not meant for determining “you can expect this DPS in recount” numbers, but more like “your gear is good enough now that MM is more DPS than SV for stand and shoot fights” type of information.

  8. silentdante says:

    when i hit 80 i went to probly 18 websites that feature hunter guides, what equipment to get, what builds to use, and i got the most out of this site.

    Not to say some other sites dont give some good advice, but for hunter knowledge and testing base i will always come here first to figure out how to improve myself.

  9. Brigwyn says:

    Interesting post. I can’t say that I completely agree with everything.
    I do agree that one shouldn’t base their entire decision on gear, spec, shot rotations on what the spreadsheet tells them. One should use all of the information and tools available to them including spreadsheets and tools like RAWR.

    As such, us bloggers that host, manage, recommend spreadsheets and other tools always understand that it is a model and never claim to be 100% accurate. But this doesn’t diminish their usefulness.

    As Eurpides pointed out. The spreadsheets are more of a guide that allows you to put in buffs talents etc…to see if you can expect an increase or decrease. As for the actual numbers? I think we all know that you can never achieve the same numbers in the game. Even manual calculations tend to be off.

    But I give credit to Wertez and Shandara for their hard work. Just as I do for you guys and the other theorycrafters out there that keep us all in check.

    Keep up the good work!

  10. MrJackdaw says:

    Struggling to word this…

    Thank you for the very extensive time you put into this and release to us – the general public – for absolutely no gain to yourself. You could just keep all the information and results private, and the fact that you do share it is truly staggering. Thank you!

  11. Lynux says:

    my brain hurts.

  12. Arthemystia says:

    Kinda like Frost, I’ve only been using Shandara’s SS for gear choices. It’s as reliable a source as I’ve found for calculating EAP values, and makes my life easier than trying to figure it out on my own (though I’m also running Pawn for a second opinion, with approximated values for stats that are posted in the WHU’s EAP guide).

    But I’ve found too many discrepancies between either my own experience and the spreadsheet, or Frost’s rigorous testing and the spreadsheet, to trust it for anything beyond gear decisions.

    So agreed on all counts. For those that are disagreeing on the grounds that the spreadsheet is just a tool, guide, etc. I doubt Frost would disagree with you. That is the intended purpose, and the spreadsheet does a great job being useful. The problem is the people that DO use it as their only source of decision-making. And many such people exist.

  13. Frostheim says:

    MrJackdaw, you need to create an account!

  14. pawelski says:

    Ya, it’s pretty much a method for quantifying gear value. The thing about even true theorycrafting and dummy-testing in general is that only a few fights are really like that. Most have tank and spank aspect built around more complicated phases that require movement and/or utility. For example, the whole Marksmen vs SV thing – for me, hands down MM has better overall DPS against a training dummy or a Patch-like fight (I know because I can pull 6.5k on patchwork with MM and only 6.1k (maximum) w/SV with more or less the same raid buffs/target debuffs). The thing is, in Ulduar, there is so much going on other than tanking and spanking, Explosive Shot equipped SV hunters can pop explosive shot which is the highest damage shot in the arsenal and that will carry on for 6 seconds and you’re still doing large amounts of dmg even if you aren’t pounding away on your cooldowns. You can then be more of a utility player – MDing, CCing, adds, whatever it may be. When you take MM to that same complex Ulduar fight, you have so many cooldowns that not-focusing on your DPS will take a larger toll. Of course, a skilled MM hunter will manage, but depending on the fight, SV can still take home the gold. Now, the whole point of this comparison is that on Shandara’s Spreadsheet, you have your MM showing up significantly higher than your SV, but you SV could very well have produced better results in the fight. My point is not that SV is a more versatile build, I don’t believe that, but rather that DPS is situational, so you can get good ideas from theorycrafting, but as Frost has said multiple times, with the RNG and various style battles in Ulduar, having skill and knowing the fight will put you higher on the dmg meters than having the PERFECT DPS setup. Sorry this is so long.

    Paws

  15. Luckedout says:

    I’m glad you go through all that work Frost bc that would bore me to death! Instead I just come here, read your hardwork, try it out and if I like how things feel I keep it. Hasn’t failed me yet! Keep up the good work!

  16. troublewithtribbles says:

    am I allowed to say that I hate that $%()$*# spreadsheet? I think I’m just too darn slow to get it to work. Either that or it simply doesn’t like my mac-based excel. It errors out every time.

  17. Myrliandre says:

    So an interesting post Frost, …

    Raises a question / point which I’ve been at some contention with you over for a while, but haven’t mentioned, which is your definition of “Theorycraft” – and to extension, what part of the puzzle it addresses.

    On the whole I agree with your statements — Spreadsheets are not the whole picture & that experimentation is an important part of answering the question “How do I get the best out of my character?”

    My contention is that empirical data collection is not the same concept as theorycrafting. In my mind I divide the two data sources — just like the division between “Experimental Physics” and “Theoretical Physics” — To *me* Theorycrafting is the developing of the models aimed at developing a simulation of the game mechanics.

    Using these models to predict in game behaviour and help make decisions on gear or glyphs is ok, but hinges on the complexity of the simulation process used .. To date spreadsheets have very simple simulations — about the only fight even close to the simulation is Patchwerk.

    Answering the questions that involve more complex situations, require more complex simulators. OR the development of empirical experiments (in practice both).

    When you present your “scientific” exploration of topics, I see it as a step beyond the definition of “Theorycraft” because you take it to the operational experimental stage. As oppose to developing experiments to test the theoretical models.

    To me, you’re addressing the questions of “what is better” rather then “extending theorycraft” (i.e. improving the models)

    Regardless … Anyone who claims the models in the spreadsheets are 100% right, is crazy, and anyone who believes the simulations the spreadsheets run are representative of real fights is likewise loony. Spreadsheets offer an interesting perspective – a good point to produce hypothesis for empirical testing too – but are not the be all and end all of knowledge.

  18. Armin says:

    Very good post!

    An example of some spreadsheet hunters: look if they have the Kill Shot glyph. Most useless non-PvP glyph, but many hunter’s take it to take them higher on the dps. The sheet told them it does …

    In reality it is just a show-off glyph they can take out of their pocket when the raid-team already got the boss down to 20% (or wiped X times trying). The thing is the team will also get it down then without the speardsheet-showoff hunter shaving of a few secs of the fight and pushing him upwards on the recount-logs. He’d better glyphed on something that helps the raid also during the first 80%.

    (And yes, there are those one or two multi-stage bosses where this is different. Sure, but did you honestly glyph on purpose for that boss or are you just trying to find excuses? ;-) )

  19. Myrliandre says:

    Not sure I’d use “Kill Shot Glyph” as a good measure,

    Bosses that enrage or change tactics (added damage) at low health are not uncommon, and the “end of the fight” can sometimes be a higher pressure situation than the start of the fight. Being able to down the last bit faster is often an advantage — and definitely when pushing new content.

  20. Susan says:

    I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

    Susan

    http://onlinemariogames.net