Recreating the thread for my tanking spreadsheet. Old thread is here.

To quickly describe it again, it's a sheet focused on accurately ranking gear by calculating your time-to-live, burst time-to-live, threat-per-second, and other stats, in your current gear (use the fitting room to specify).

It derives armor equivalency point (AEP) scores for the different stats, and uses them to score pieces of gear. The calculated values are presented as a suggestion, you are free to place any values at the top of the gear list.

It takes care of all your gemming for you at your chosen quality level. It analyzes the value of the socket bonus and decides whether you should stack your best gem (usually full +stam) or try to get the socket bonus. There is a threshold you can adjust in case you like to meet socket bonuses when the decision is close. If the socket bonus is worth getting, it also calculates the best gems to place in each slot based on the stat weights.

The sheet is upgraded for 3.1.

NOTE: There are now two versions of the sheet, one with a sorting macro installed, and one without it. The macro version will pop up a security warning for most people. If that annoys you, download the nomacro version. All it does is sort the gear list, to make it easier to play around with stat weights.

OpenOffice.org version (has sorting macro)
Excel version (no macros)

ALTERNATE LINK - Savefile.com OpenOffice version (has sorting macro)
Last Updated: May 24, 2009

Here's the first big upgrade for level 80. Still need to sort through the new trinkets, but all the other slots are updated with new gear. Here's the changelist:

- added a fourth parameter for weighing stats, this one focused on DPS, you can give it a value on the Options sheet
- updated abilities to level 80 damage and innate threat values
- updated combat ratings to level 80 conversions
- updated buffs to level 80 values
- added new chest pieces from WotLK
- updated all gems to WotLK versions; also added strength, crit, and expertise as gem stats
- removed “heroic” gem quality
- added new boots from WotLK
- updated a lot of formulas to assume 400 base defense
- added rage conversion constant to Numbers sheet and added it to relevant formulas
- updated armor damage reduction formulas to level 80/83
- added new gloves from WotLK
- added new helms from WotLK
- changed the way meta gems are detected (little cleaner, less prone to error)
- gave meta gems the AEP of the best gem from the Gems sheet
- added new shoulders from WotLK
- added new legs from WotLK
- changed leatherworking armor kit to the new 18 stam version
- added new necklaces from WotLK
- added resilience to stat calculations (finally)
- updated item upgrades/enchantments
- changed default gear set to heroic WotLK
- added new cloaks from WotLK
- repaired revenge damage calculation
- added new bracers from WotLK
- added new belts from WotLK
- added new rings from WotLK
- added new shields from WotLK
- added new ranged weapons from WotLK
- added new weapons from WotLK

I got back to work on this quicker than I'd expected. This one theoretically has all the new gear, including trinkets. Here's the changelist:

- added new trinkets from WotLK
- updated all trinket special abilities to calculate strictly based on uptime, it's not perfect but it works somewhat and it's not completely arbitrary
- added a new gem quality, “perfect” for the perfect green gems - select this on the options sheet if you plan to buy a lot of perfects
- added a calculation for the value of the +2% armor from items on the Austere Earthsiege Meta (this is an awesome meta gem)
- added Austere Earthsiege effect to Toughness bonus, if equipped

This thread has been very quiet so far, but I would just like to say that this is very impressive work, and I am very grateful to you for putting it together. I'm looking forward to using it to try different gem loadouts and such as I hit 80 and get back into raiding. Thanks, and keep up the good work.

One change I made to my copy that I liked was to add a row to the Fitting Room with desirable ratings for things like how much defense rating for crit immunity, how much hit rating for +9%, how much expertise rating to hit the dodge cap, etc. It's nice to see those values as I compare gear sets.

Great work, noticed two missing things (or I'm blind).
[Breastplate of Tormented Rage]
[Glyph of Blocking]

Great spreadsheet! It's certainly very useful for gearing up as I approach level 80. One problem I am having at the moment is selecting a Helm which doesn't contain a Meta Gem socket. It appears to bug out the AEP calculations resulting in '#VALUE' appearing.

I was throwing together a random set of gear and giving a stamina gun one of each DPS stat with ttl weighted 0.001 and dps weighted 0.999. A couple of the differentials in AEP were surprising:
str: 50
agi: 67
block value: 28
hit: -6
crit: 80
exp: 10
ap: 11
haste: 74
armor penetration: -6

Probably need to add a couple checks somewhere in the DPS calculations to avoid counter-intuitive negative values. Presumably in this case hit was already over the cap and the designated mob's armor was already completely negated.

So part of the problem here is when you make DPS worth so much more than tanking, it tries to scale everything up so that armor is still worth 1 AEP. And since more armor means less rage which potentially means fewer heroic strikes, it can be negative.

For the next release I'm making it decide whether to scale up to armor being 1 or to AP being 2. So if you make DPS very important, it will switch to an AP-relative system.

It's possible to hit the ArP cap but it's like 1540 rating so I'm not sure how you did it. I added a check anyway to make sure you can't break the cap.

Just double checked. The unbuffed total of ArP in this scenario 29.5 -> 30.5, and "Off miss" goes down with +1 hit from 1.13% to 1.10% so my speculation about the caps is probably off base (was just a wild stab based on what I'd seen in past dps spreadsheets). The negative AEP value seems to be coming from the 1h weapons, as they all seem to decrease in AEP value when raising hit or ArP on the ranged weapon. The AEP of the ranged weapon (and the helmet) increase in value, just by less than the weapon's AEP decreased.

The DPS total in C86 on the stats tab goes up with all the DPS stats, however, so your conclusion that the AEP calculations and atypical weighting is at play looks to be on the mark. I'm not sure how to track down the item by item AEP calculations, so I'll not speculate any further. I can get the information I was looking for from the DPS total number.

Very appreciative of all the hard work that went into the spreadsheet, btw. It's a great resource.

Excellent spreadsheet. Experiencing same problem referenced by Console above.

This release should fix those meta gem problems. Here are the changes:

- added Breastplate of Tormented Rage
- fixed a bug with selecting a helmet with no meta gem (hopefully)
- added Glyph of Blocking (assuming it's additive with other block buffs)
- added Glyph of Mortal Strike
- updated the amount of armor sunder removes
- adjusted boss and trash stats a little, this is all still pretty grey at the moment
- removed BC bosses from list
- changed defense calculation to TRUNC to err on the side of caution
- changed the AEP values to be AP-relative when you want DPS/TPS so badly that armor becomes unimportant
- added a check to make sure you never have more than the cap for armor pen (though it's something like 1500 rating)
- changed Enchant Chest - Mighty Health to Enchant Chest - Super Health (now adds 275 HP)

The gem choices tab seems to be missing a section for the neck slot item.

Tell me if I'm wrong, but you forgot to add the [Dragonscale-Encrusted Longblade].

P.S. You did great with this spreadsheet, I don't know how to thank you. Keep up the good work, sir.

Not sure if Diminishing Returns on avoidance was already in before WOTLK or you already have it working, but I would assume time to live would need to factor it in.

Kage:
There's definitely a few more sockets I'd like to account for. Necks can now have gems, and now blacksmiths can add gems to anyone's belt, and to their own bracers and gloves. I've been stuck on how to implement JC gems, but hopefully I can get that all in there soon.

Bruise:
Looks like I accidentally deleted the longblade when I added all the new ones. The list is pretty long so I tried to trim it down. Now that there's lots of better options (including an easy quest in zul'drak), I doubt I'll add it back.

Sabre:
Diminishing Returns have been in the sheet since shortly before 3.0 broke. However I can only guess that the miss cap is 50, since we have no hard data. If anyone has seen any hard data, even for a very rough estimate, please link me to it.

Maybe you add [Indestructible Potion]

You can use it Twice in a Fight,
First befor the pull -> 2Mins minus Time you need to Pull.
Second if the Boss enrages you take it then until the end. (IE Maexxna)

I've been using this a lot lately to get a feel for different gearsets and what will be the best overall for damage reduction. However, one thing I've noticed is that the health calculations in the "final stats" section seems way, way off and I can't figure out why. For example, my current gear on live has me just shy of 32k HP unbuffed. Yet putting it all in to the spreadsheet it shows the unbuffed HP level at something like 27k.

The spreadsheet is also missing updated foods/elixirs/consumables (40 agi 40 stam Dragonfin, 40 expertise 40 stam Rhinolicious Wrymsteak, 45 Agility elixir, 800 Armor elixir)

It's also missing Lavanthor's Talisman, but editting that in isn't so tough. And belt buckles!

Finally, you may want to change the boss' default swing DPS to be 20k, because that's about appropriate for the tougher bosses right now where survivability is actually a concern.

<Edited some out for stupidity.>

Health is still off because I haven't updated racial stats at 80, nor warrior base health at 80. I'm working on that, lots of gem stuff, and consumables as we speak, hopefully can get all this stuff done in the next couple days.

First, thank you Talia for all the hard work on the spreadsheet. A quick fix for people wanting to add buckles or who have blacksmithing is to just add a blue gem (assuming you'll be putting 24 stam into those slots) to each belt item (wrist and gloves if your a BS) on the gear list tab.

Ok, based on WWS, my back of the envelope calculation puts Patchwerk at upwards of 27k DPS to an unarmored player. I'm going to set the standard boss DPS to 24000 for now, pending further investigation.

New version up:

- added Lavanthor's Talisman
- changed base boss DPS to 24000
- added new buff foods, flasks, and elixirs from WotLK
- updated base racial stats and base health
- added Tauren racial health to Stats sheet calculation
- added the JC-only gems to the Gems sheet, you must manually select 3 of these gems on the GemChoices sheet if you're a JC
- updated a couple of item sources
- made burst reduction a little more important again
- added possibility for gem slot to neck
- added a free gem slot to belt on GemChoices sheet, assuming you will want to use an Eternal Belt Buckle (they're cheap)
- added a blacksmith toggle to Options sheet
- added gem slots to wrists and hands on GemChoices sheet, will activate if you set the blacksmith toggle to 1
- added Master's Inscription of the Pinnacle to shoulder enchants (inscriptors only)
- noted that Horn of Winter is interchangeable with Strength of Earth totem
- noted that Imp. Icy Talons is interchangeable with Imp. Windfury

Thanks for this spreadsheet, it really helps me a lot when making gear decisions. I just wanted to point out that the stats for Amulet of Wills is slightly off. The spreadsheet lists it as having 25 defense, but really it is 25 dodge.