Jump to content


Tanking Spreadsheet

  • This topic is locked This topic is locked
339 replies to this topic

#1 Taliafears


    Piston Honda

  • Members
  • 162 posts

Posted 18 November 2008 - 09: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

#2 Taliafears


    Piston Honda

  • Members
  • 162 posts

Posted 18 November 2008 - 09: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

#3 Taliafears


    Piston Honda

  • Members
  • 162 posts

Posted 19 November 2008 - 11: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

#4 Agrimat


    Are you using Shield Block?

  • Members
  • 291 posts

Posted 20 November 2008 - 01:47 AM

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.

#5 Ultramax


    Soda Popinski

  • Guild Members
  • 2844 posts

Posted 20 November 2008 - 02:16 AM

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

#6 Console


    Glass Joe

  • Members
  • 13 posts

Posted 20 November 2008 - 09: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.

#7 azador


    Von Kaiser

  • Members
  • 49 posts

Posted 20 November 2008 - 02:00 PM

Great job on this spreadsheet. Thanks for the effort.

#8 Olon97


    Von Kaiser

  • Members
  • 69 posts

Posted 21 November 2008 - 08: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.

#9 Taliafears


    Piston Honda

  • Members
  • 162 posts

Posted 21 November 2008 - 03:58 PM

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.

#10 Olon97


    Von Kaiser

  • Members
  • 69 posts

Posted 21 November 2008 - 04:39 PM

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.

#11 North


    Glass Joe

  • Members
  • 7 posts

Posted 24 November 2008 - 04:18 PM

Excellent spreadsheet. Experiencing same problem referenced by Console above.

#12 Taliafears


    Piston Honda

  • Members
  • 162 posts

Posted 24 November 2008 - 05: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)

#13 Armaul


    Glass Joe

  • Members
  • 9 posts

Posted 25 November 2008 - 12:08 AM

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

#14 Kagetora


    Glass Joe

  • Members
  • 4 posts

Posted 25 November 2008 - 06:38 PM

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

#15 Bruise


    Glass Joe

  • Members
  • 2 posts

Posted 26 November 2008 - 01:32 AM

Tell me if I'm wrong, but you forgot to add the .

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

#16 Sabrewarrior


    Glass Joe

  • Members
  • 9 posts

Posted 26 November 2008 - 06:11 AM

- updated combat ratings to level 80 conversions

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.
meuw le meuw, mush ri mush, grum so grum

#17 Taliafears


    Piston Honda

  • Members
  • 162 posts

Posted 26 November 2008 - 06:35 AM

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.

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.

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.

#18 Glory


    Von Kaiser

  • Members
  • 93 posts

Posted 26 November 2008 - 08:59 AM

Maybe you add

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)

#19 North


    Glass Joe

  • Members
  • 7 posts

Posted 26 November 2008 - 03:02 PM

Missing inscription self-enchants to shoulder:

master inscription - Wowhead Search

Thanks for your efforts.

#20 Xav


    Bald Bull

  • Members
  • 2469 posts

Posted 01 December 2008 - 02:26 PM

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.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users