The Joy of Spreadsheeting

This is not gonna be a fun post for me because, well, I goofed. I made some seemingly innocuous changes to the spreadsheet a while ago that caused it to be less accurate than it normally is (which, for the record, has never been anywhere near 100%), and it’s taken me a while to figure out that there was a problem and then finally fix it.

The short answer is that the spreadsheet has been overvaluing crit since 7.1 was released. In this updated version, you’ll see stat weights that look way off. Specifically, crit is much lower and haste is much higher; this is due to several reasons. The first is the previously mentioned changes that caused crit to be inflated. Second, I had been manually reducing the weight of haste by 10% because of its adverse relationship with mana; I’m no longer doing that. Last, stat weights are going back to what appear to be much more reasonable values, but everyone has been under the impression that crit was so powerful, so they have gravitated toward gear with lots and lots of crit. This makes crit look weaker than you would expect simply by virtue of having so much of it and so little of everything else. By comparison, the other stats will be higher than expected until stat distribution evens out a little more.

Is the World Ending?!

Even though it’s very likely that some of you will actually see haste as being your highest valued stat and crit near the bottom, this doesn’t mean that our stat priorities have really changed. I’ll concede that haste has more value than I originally thought at the beginning of the expansion (partly why I’m getting rid of the 10% reduction), but that’s mostly due to our capability to take incredibly mana-efficient talents without hindering our throughput too much. Crit is still our best overall stat, it just might not appear that way because you have so much of it.

To illustrate this point, I want to show two graphs. The first graph shows the relative value of each stat when they’re all balanced equally:

Relative value of secondary stats compared to each other without factoring in Intellect. Versatility is chosen as a baseline. Each stat starts at 6000 and steadily increases by 1000.

This graph shows our stat priorities. Crit is much higher before the 30% (25% with 2-piece Tier 19) breakpoint but remains the best until the 50% (45% with 2-piece Tier 19) softcap, where it plummets off the chart. Haste is slightly higher than mastery because I’ve removed the 10% reduction.

This second graph shows the relative value of each stat when you “stack” only crit and haste:

Haste is chosen as a baseline. Each stat starts at 6000 but only crit and haste steadily increase by 1000.

Here, crit and haste have the same trends, but the value of mastery and versatility are constantly increasing rather than staying relatively even, like in the first graph. Even though some stats are better overall, focusing heavily on certain stats and disregarding others leads to this situation where lesser stats become more valuable. To an extent, this is what most of us are likely seeing on the spreadsheet.

What’s the Moral of This Story?

I may have lead some of you to believe that crit was more powerful than it should have been, and for that, I’m sorry. However, as I hope to have now demonstrated to you, a balance of secondary stats isn’t terrible for you. The fact that the spreadsheet is showing crit as worse than other stats isn’t because I’ve completely altered how everything works and crit is now a bad stat for us. You probably just have a ton of crit and very little of other stats.

I’ve never explicitly said that crit should be stacked or that any other stat should be avoided, but I can see how it might seem that way. As I’ve maintained in the guide here and on Wowhead, item level is typically the best indicator of an upgrade, at least when it comes to items that aren’t relics or trinkets. I don’t mean to say that an item level increase will always be an upgrade; that’s definitely not true. But a general rule of taking the highest item level regardless of the secondary stats is not going to put you at any meaningful disadvantage and is much less complicated. Nighthold is coming out soon, so you’ll have plenty of opportunity to shuffle around stats with new gear.

---

So, What Actually Happened?

For those of you still with me, I’d like to explain where I went wrong if for no other reason than to share with you part of the nihilistic nature of trying to model healing on a spreadsheet. Fair warning: the rest of this is probably not very interesting to most of you.

First, I should explain the basic premise behind my model. Almost every ability with a small cooldown (less than 30 seconds) is an increase to both healing per mana and healing per second over spells that don’t have a cooldown, like Flash of Light or Holy Light (in this case, an Infusion-of-Light-buffed Flash of Light or Holy Light is categorized as a cooldown because it’s tied to Holy Shock). Obviously, that means we want to use them any time we have the opportunity and then use the rest of the time on the “filler” spells like Holy Light and Flash of Light.

My model calculates the amount of time it will take to cast all of those smaller cooldown abilities in one minute and then relegates the rest of the time to filler heals. Put as a formula:

Filler time = (60 – (n * GCD)); where n is the number of cooldowns cast in one minute, and each cooldown takes the same amount of time to cast as the global cooldown (GCD).

To figure out how many times we cast Flash of Light in a minute, I divide that filler time by the cast time of Flash of Light (which is always the same as the GCD). The problem came when I realized that most Holy Paladins weren’t actually casting filler spells as often as my model predicted. The casts were closer to one-third as often as I predicted, but the cooldowns were being used almost exactly as often as I predicted. To remedy this, I simply multiplied the number of filler spells by 0.3.

For most talent combinations, my average casts per minute were on target with what logs were showing. However, adding more cooldowns through talents creates issues. Adding more cooldowns artificially increases the total time spent casting rather than keeping it the same. I think this is more easily explained through an example. Assume that I have 0% haste (just to make things clean), so the GCD is 1.5 seconds, my talent combination is using a total of 30 cooldowns (n) in one minute, and all filler spells are Flash of Light (1.5s cast time).

Filler time = (60 – (30 * 1.5))
Filler time = (60 – 45)
Filler time = 15 seconds
Number of Flash of Lights = 15 / 1.5 = 10

But remember, we don’t cast Flash of Light that often, so that final number is multiplied by 0.3, resulting in 3 casts of Flash of Light per minute. This gives us a total of 33 casts per minute (30 cooldowns + 3 Flash of Lights).

Now, let’s assume everything else is exactly the same except that we want to add one more cooldown per minute (n = 31). In reality, we would drop one cast of Flash of Light to make room for this additional cooldown, but that’s not what was really happening.

Filler time = (60 – (31 * 1.5))
Filler time  = (60 – 46.5)
Filler time = 13.5 seconds
Number of Flash of Lights = 13.5 / 1.5 = 9

We see that we’ve dropped a single cast of Flash of Light, but we still have to multiply by 0.3, giving us 2.7 casts of Flash of Light per minute. Then the total becomes 33.7 casts per minute (31 cooldowns + 2.7 Flash of Lights). By adding one cooldown, we’ve gained 0.7 casts per minute, or 1.05 extra seconds of casting time.

None of this is all that complicated on the surface, but the problem was that this was buried in lots of other arduous calculations and wasn’t immediately apparent. I honestly can’t explain to you why this made crit so much more valuable than any other stat. I have guesses, but I’m still not entirely sure. Regardless, the problem went a little further.

My attempt to solve this issue was to create “dead globals”. Essentially, these are times when you might miss casting a heal due to movement, latency, or casting some other non-healing ability. Because most of our non-filler abilities are instant, we can still cast them on the move, so our filler spells are usually sacrificed as a result (this is why Flash of Light casts were so wrongly predicted by my model, but the cooldowns weren’t). Unfortunately, I was under the impression that these dead globals should be dynamically adjusted based on your talents. I don’t know why I assumed talents would have an effect on this; Crusader Strike from Crusader’s Might and Judgment from Judgment of Light are both already classified as one of the cooldown spells, so there’s not really any reason for talents to change how often you miss casts.

For the past few months, there’s been a cell for these dead globals that was changing based on how many cooldowns you were using. This was obviously wrong and has since been changed, but the result is much less value for crit. I knew that there was a problem and that this solution wasn’t correct, but I didn’t realize how much of an effect it was having.

So, my bad.