Tanking Spreadsheet

Taliafears · 11/18/08, 4:26 PM

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

Taliafears · 11/18/08, 4:31 PM

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

Taliafears · 11/19/08, 6:30 PM

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

Agrimat · 11/19/08, 8:47 PM

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.

Ultramax · 11/19/08, 9:16 PM

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

Console · 11/20/08, 4:38 AM

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.

azador · 11/20/08, 9:00 AM

Great job on this spreadsheet. Thanks for the effort.

Olon97 · 11/21/08, 3:40 AM

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.

Taliafears · 11/21/08, 10:58 AM

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.

Olon97 · 11/21/08, 11:39 AM

Originally Posted by Taliafears

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.

North · 11/24/08, 11:18 AM

Excellent spreadsheet. Experiencing same problem referenced by Console above.

Taliafears · 11/24/08, 12:51 PM

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)

Armaul · 11/24/08, 7:08 PM

Confirmation on Glyph of Blocking being additive: Warrior Glyph of blocking - TankSpot . Thanks for fixing the no meta socket in helm issue so fast!

Kagetora · 11/25/08, 1:38 PM

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

Bruise · 11/25/08, 8:32 PM

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.