Theorycraft Episode V:harlekin's DR Spreadsheet Strikes Back

I have revised my spreadsheet somewhat. I essentially rebuilt a lot of it to include more stats and base/talented stats. It is most certainly a work in progress, so any feedback and bug reports are appreciated.

Without further ado:
harlekin's DR Spreadsheet (*updated 2:08pm CST 12/18/08*)
No Excel, no credit? No problem! It should work fine if you import it into Google Docs. (Someone please test this, I'm no longer certain it will work due to the interactive stuff I added)

What is it?
This is a spreadsheet intended to help you decide what gear is better. There are many spreadsheets like this out there. This one considers Diminishing Returns on all avoidance including miss. The most important addition from my previous spreadsheet is the inclusion of block. Now you can see estimates for your block cap without doing the math yourself! Keep in mind that miss DR is still an estimate, so 102.4% on the spreadsheet could be over- or under-capped by a small amount. Rather than hoarding my work to make myself better than all of the other tanks out there, I've decided to give it away free and achieve my fame via forums. >.>

The core of support for all of the plate tanking classes is in there, but I have to go back and do a lot of work to actually make it work for non-paladins. [edit] Dodge from agility is currently accurate for paladins only, all other avoidance DR numbers not counting base/talented should be applicable to warriors and dks. [edit] Now working (mostly) for all plate tanks. I need help with the base values and testing.

Bug List:
-----------
Base strength is not added in yet - workaround: just add it in to the starting column. FIXED
EH calculations need improvement (no AD, god knows what else) - improved, still needs work
Defense skill rounding needs to be addressed FIXED
Armor calculations need work, no differentiation between bonus (no multiplier) and base
Many base values are wrong (base hp in particular, some of the non-paladin values)

Wish List:
------------
I tried adding TTL calcs, but they were terrible. If anyone knows a good TTL calculation that can be done in excel, please let me know.
Full Warrior/DK support is doable, I just need the time/motivation Implemented - need correct base values, testing
Make race selectable DONE - need correct base values though
Make kings selectable DONE

This spreadsheet started with my original task to discover whether defense or dodge was better. The original thread can be found here: http://www.failsafedesign.com/maintankadin/viewtopic.php?t=17390
_________________
Shadow Council - US - Horde
Xandreth (74) - Prot Paladin - MT (going ret until 75)
Tyrenn (80) - Frostfire Mage
Saege (70) - Resto Druid in training
Staub (63) - DK backup tank

Looks good. Can you split the total avoidance into miss/dodge/parry categories? That way people can compare directly to their character sheet numbers to check if they are doing things right. It will also help with detecting and tracing bugs.

Also, the non-base stuff should work okay for warriors and DKs as long as no agility is involved, as they have a slightly different agi:dodge ratio.
_________________

Wow, Warriors and DKs have the same agi to dodge conversion, but I didn't realize that it was so much better for pallies. I'll update my notes on that.

I'll add some extra cells for the avoidance breakout. I'm not positive my base/talented numbers are right, as I'm not on my home machine to crosscheck everything, so I definitely appreciate the feedback.
_________________
Shadow Council - US - Horde
Xandreth (74) - Prot Paladin - MT (going ret until 75)
Tyrenn (80) - Frostfire Mage
Saege (70) - Resto Druid in training
Staub (63) - DK backup tank

Thanks, I'd been looking for something like this.

A few observations after trying to match my character sheet stats:

http://www.wowarmory.com/character-sheet.xml?r=Tanaris&n=Hammerinhank

- The rounding (flooring) for defensive skill is not taken into account, so that i effectively have 149.8 defensive skill instead of the floored 149 skill.

- The base agility varies slightly between races (see http://www.wowwiki.com/Paladin_attributes), i'm not sure of the base agility for the different races, for my level 80 dwarfadin it appears to be 86.

- The agility entered in the gray section is agility from gear alone, not total agility. Strength and Stamina appear to be totals.

- The effect of blessing of kings is included in dodge from gear-based agility calculation, not total agility (I don't know which is correct). Note that character sheet dodge is computed without blessing of kings at all.

These minor tweaks don't change the total avoidance numbers much, they may be overly optimistic by a tenth of a percent. This is probably insignificant in light of the uncertainty regarding miss DR. (ok, i'm just obsessive about matching my character sheet).

Thanks again for your efforts.
_________________
US/Tanaris: Hammerinhank - Dwarf Paladin | Toastmonger - NE druid | Weedwhacker - Gnome Mage | Shugsie - Gnome Rogue

This is great. Sticky plx!
_________________
Lies! You just don't want us to see your super secret strat involving Voodoo Gnomes!

This is a bit off for me, I think. Getting ~1.5k more health than I'm supposed to have, and getting avoidance values really off, dodge is ~1% too high, parry ~1% too low, and block ~2.5% too low.

Wrote the stats direcly off my character pane, any thoughts of why it's off?

Maybe its taking into account the new 8% from talents? I too have about 1.5k more HP.
_________________

Armour isn't being given by Agi! (Should be 2 armour / Agi)

Also, you might want an option to have kings on or not. (I think at the moment you automatically assume kings?)

This is important as I was trying to work out when Agi becomes better than Dodge rating for TTL!

[edit] Ah, nm you are including the armour from Agi, but only in the EH cell, rather than in the Armour cell as well. I suppose either way is as logical but I was expecting to see it in the armour cell [/edit]

Thanks for all the feedback. I will start adding some selection boxes to select race and buffs, but it may take me a couple of days. Some of the other bugs I should be able to fix sooner.

I'm thinking I should add another total at the bottom for armor. The totals at the top are by row and shouldn't contain combinations (armor from agi), but I hadn't displayed the actual armor total anywhere. I should also try to figure out how to account for the armor bonus changes that are coming in 3.0.8. Bonus armor from enchants is already treated this way, so I guess that could be considered a bug already.

I will try to track down the bugs that are causing some of the numbers to not match character sheets. If someone could post the information that they entered and the actual character sheet values (pre and post DR) that would help me figure it out.

[edit]
If anyone has a list of base stats for the various race/class combinations, I have the framework in place for selection of your race and class. I just need the numbers to fill into my data tables.
_________________
Shadow Council - US - Horde
Xandreth (74) - Prot Paladin - MT (going ret until 75)
Tyrenn (80) - Frostfire Mage
Saege (70) - Resto Druid in training
Staub (63) - DK backup tank

I've posted a pretty significant update to the spreadsheet. I implemented class and race selection as well as a toggle for kings. I'm not sure if the spreadsheet will work in google docs anymore with these features though.

I need the base and talented values for the various race and class combinations, so if anyone could post them I would appreciate it. (I do most of my theorycrafting in the odd bit of spare time at work, behind a draconian firewall. Can't let my theorycrafting get in the way of WoW time ) I need base stam, agi, str, hp, armor multiplier, stam multiplier, str multiplier, and probably a few other things. Some of the numbers for DKs are tricky, as they vary between the specs (veteran of the third war comes to mind), so I'm not sure what to do about that in the short term.

I made a lot of corrections to the formulas and the way base stats are handled. Hopefully it will be more accurate now. You can toss your character sheet agility in the top section now and the base amount should automatically be subtracted from it where appropriate. I think kings is handled properly in these situations too now.

Thanks again for the feedback. If you find any bugs, please give me the values you entered into the spreadsheet along with your character sheet values (pre and post DR if possible). The more data I have to work with, the more effective my debugging will be.
_________________
Shadow Council - US - Horde
Xandreth (74) - Prot Paladin - MT (going ret until 75)
Tyrenn (80) - Frostfire Mage
Saege (70) - Resto Druid in training
Staub (63) - DK backup tank

so under starting, put what we see on our character sheet,

and what do we put under replacing?

sorry late at night..

The replacing column is for items that you want to replace with other items.

E.g. If you want to replace the Tempered Saronite Gauntlets (TSG) with either Daunting Handguards (DH) or with Refined Ore Gloves (ROG), enter the stats of the TSG in the "Replacing" column, stats of DH in "Item 1" column and those of ROG in the "Item 2" column. You can check the change in stats in the rows below.

Harlekin, if I may make a suggestion, can you add one more row in the results (before the item 1 and item 2 rows) that shows the original stats?
_________________

By original stats, you mean based on the character sheet values? Yeah, I think I could do that.

I still have a call out on base stats! A lot of the numbers you get in the spreadsheet right now are incorrect because I don't know the base values. All of the DR and rating stuff should be fine, but for totals a lot of the numbers are off.
_________________
Shadow Council - US - Horde
Xandreth (74) - Prot Paladin - MT (going ret until 75)
Tyrenn (80) - Frostfire Mage
Saege (70) - Resto Druid in training
Staub (63) - DK backup tank

Excellent! - current version matches my character sheet avoidance exactly. I am a dwarf paladin with the standard protection talents. Now for more gear twiddling...

Note that you'll probably need to add an option for the block value meta gem http://www.wowhead.com/?item=41396 to improve EH/TTL calculations. I'm not positive, but I believe this stacks additively with the block value talent, so that instead of multiplying BV by 1.3 (+30%), you just multiply by 1.35 (+30% +5%) when the meta is active.[/url]
_________________
US/Tanaris: Hammerinhank - Dwarf Paladin | Toastmonger - NE druid | Weedwhacker - Gnome Mage | Shugsie - Gnome Rogue