Closed Thread
Page 1 of 14 1 2 3 11 ... LastLast
Results 1 to 20 of 263

Thread: The Cataclystic Protection Warrior Spreadsheet

  1. #1
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336

    The Cataclystic Protection Warrior Spreadsheet

    Prot Warrior Spreadsheet v4.2.0a.zip

    The Spreadsheet should now be completely up-to-date on Cataclysm v4.2.0
    As of now, all spreadsheets will have patch numbers in their name, so it is easier to keep track of changes. In case of hotfixes with substantial changes they will be called v4.x.x_Hx
    If you encounter any bugs or issues, post in this thread and we'll help you as much as we can.

    Short explanation of the pages:
    (Most explains itself)

    1. Stats
    * Fill in all the yellow cells with Armoury numbers. The numbers next to it will show the effective in-raid numbers.
    * Fill in boss damage for Damage Reduction tab.
    2. Talents&Buffs
    * Tab to adjust raid buffs, talents, glyphs, set bonuses,...
    3. Abilities
    * Detailled math on all numbers per ability. (Average Damage/Threat, Damage/Threat Per Rage,...)
    4. Rotation
    * Short table to calculate your rotation's total DPS & TPS (Rend = full 6 ticks, BnT ticks can be added to Thunderclap below it)
    * Perfect to check TPS from combat log parses or to adjust priorities/rotations on target dummies.
    5. Damage Reduction
    * All things defensive:
    * Damage Reduction from Armor & Avoidance and how much bosses actually hit you for.
    * Results of EHP, TankPoints (HP/Damage Reduction) and Burst Time (see link) models.
    * Stat values for the above models for all stats, taking into account your talents & environment.
    * Item Comparison tool for stat values of both TankPoints and Burst Time models (or a combination of both)

    Disclaimer:
    You do not need to post or PM me to ask if you can translate and/or re-post this spreadsheet elsewhere. You can, as long as there is reference to it's origin; this thread right here.
    Last edited by Airowird; 08-11-2011 at 01:37 AM.
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  2. #2
    Join Date
    May 2009
    Posts
    2,974
    Thanks again for this.

  3. #3
    Join Date
    Dec 2009
    Posts
    534
    I was playing around with hold the line uptime and i came up with this. Hope this helps with the spreadsheet Wartotem it looks nice

    Sw=swingtimer of the boss
    p=parry chance
    max uptime=(10/sw)*p
    avarage uptime= max uptime * effectiveness
    N= number of hits after a parry under HTL buff=10/sw (must be made a round number)
    effectiveness= Sum(((sw+k*sw)/10)*(((1-p)^k)*p)) + (1-p)^N where k goes from 0 till N-1

    Lets put some numbers into this so i can explain everything without you staring at all those brackets.
    Lets take swingtimer 1,5s parry chance=0,1 (10%)
    max uptime=(10/1,5)*0,1=0,6667 or 66,67%
    N=10/sw=6

    there are 6,67 swings in 10 secs and assuming HTL buffs wont overlap you'll get an uptime of 66,67% with 10% parry. Of course there is a big chance HTL buffs will overlap so we'll calculate how much the buffs will overlap in other words how effective they will be.

    (sw+k*sw)/10 time of number of swing divided by HTL duration to calculate effectiveness
    ((1-p)^k)*p chance of getting k swings without a parry followed by a parry

    Taking the values of 0 till 5 for k gives this:
    k=0: 1,5+0/10 * 1*p = 0,15 * 0,1 = 0,015
    The chance of a parry accouring after you parried is 10% this gives 1,5 sec HTL uptime or 15% effectiveness. 10% of your HTL buffs will only have 15% effectiveness.
    k=1: 1,5+1,5/10 * (1-p)*p= 0,3 * 0,09 = 0,027
    9% of your HTL buffs will only have 30% effectiveness
    k=2 1,5+3/10 * (1-p)^2 *p = 0,45 * 0,081=0,03645
    8,1% of your HTL buffs will only have 45% effectiveness
    k=3 1,5+4,5/10 * (1-p)^3 * p = 0,60 * 0,0729=0,0437
    7,29% of your HTL buffs will only have 60% effectiveness
    k=4 1,5+6/10 * (1-p)^4 * p = 0,75 * 0,06561=0,0496
    6,56% of your HTL buffs will only have 75% effectiveness
    k=5 1,5+7,5/10 * (1-p)^5 * p = 0,90 * 0,059049=0,0531
    5,9% of your HTL buffs will only have 90% effectiveness

    If you take k=6 you get 105% effectiveness which is silly and you don't need to calculate the chance for a parry after the 6th swing since the buff will have run out. So for the k=6 you get 100% effectiveness * (1-p)^6 = 0,53144. 53% of your HTL buffs will have 100% effectiveness.

    Sum(....) with k 0 till 5 = 0,20985
    effectiveness= 0,20985+0,53144=0,74 or 74% effective
    average uptime= 66,67% * 74% = 49,33%
    Last edited by Bigbad; 11-07-2010 at 07:21 AM.

  4. #4
    Join Date
    Apr 2008
    Posts
    311
    Just did a quick passover of the first tab.

    The if statement under hit % on the first tab doesn't make sense? If bonus health equals boss level?

    Where do you get the 6 expertise from with 0 rating? I thought they took away all bonus expertise from prot.

    Looks great though! Can't wait to play around with it tomorrow.

  5. #5
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336
    The IF statement was from an older version, where it was a Racials table hidden on the right.
    I've fixed it and added other racials I forgot (mostly Goblin & Worgen haste/crit racials)

    The expertise seems to be incorrect as well. I believe that comes from accidently moving all of the old Vitality bonuses to passive.

    PS: In case you didn't notice it, this is a level 85 spreadsheet, level 80 I don't bother with, because it'll be useless in 4 weeks.
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  6. #6
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    Thanks for putting the 85 version of this together WarTotem.

    "Abilities" tab
    "Damage per rage" & "Threat per rage" rows
    The top set of abilities (Shield Slam, Revenge, etc) seem to be calculating their DPR correctly (damage done divided by rage cost).
    But it appears that the bottom set of abilities (HS, Cleave, Rend, etc) are calculating their DPR in relation to white damage for some reason ((damage done minus white swing) divided by rage cost).

    "Abilities" tab
    "Concussion Blow" column
    This ability incorrectly (unless I missed something) has a 4x modifier for crit damage.

    "Rotation" tab
    "Overall TPR" & "Overall DPR" cells
    Have a minus infront of them, giving a negative DPR and TPR - I don't believe this is intended.
    Failure is not an option. It comes bundled with the software.

  7. #7
    Join Date
    Oct 2009
    Posts
    6
    Looks like you haven't included the stealth buff to the AP scalar on Thunderclap, just a heads up.

  8. #8
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    Was trying to figure out why Revenge was less substantially threat and damage than 1 target Cleave and just barely more than Devastate.
    I think you forgot to include the changes to Revenge's AP scaling that occured in the 3.3.3 patch. Revenge should now have a 31.05% AP coef. (50% increase over the old 20.7%).

    Edit:
    I think it's safe to say that Bastion of Defense will not have a 100% uptime (the +dmg component), as it's a 20% chance on a Dodge/Parry/Block. Even when reaching unhittable, it will not have 100% uptime (~74%). I'm not sure how you want to calculate that for the DMG_MOD cell, but a flat 10% increase to damage doesn't seem correct.
    Last edited by Andenthal; 11-10-2010 at 02:39 PM.
    Failure is not an option. It comes bundled with the software.

  9. #9
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336
    Quote Originally Posted by Andenthal View Post
    Thanks for putting the 85 version of this together WarTotem.

    "Abilities" tab
    "Damage per rage" & "Threat per rage" rows
    The top set of abilities (Shield Slam, Revenge, etc) seem to be calculating their DPR correctly (damage done divided by rage cost).
    But it appears that the bottom set of abilities (HS, Cleave, Rend, etc) are calculating their DPR in relation to white damage for some reason ((damage done minus white swing) divided by rage cost).
    Old HS/Cleave formulas, just never fixed

    "Abilities" tab
    "Concussion Blow" column
    This ability incorrectly (unless I missed something) has a 4x modifier for crit damage.
    Wonder how that got there.... fixed now

    "Rotation" tab
    "Overall TPR" & "Overall DPR" cells
    Have a minus infront of them, giving a negative DPR and TPR - I don't believe this is intended.
    Actually, the cycle/rage was supposed to be negative, making those 2 positive. But I have figured out that with Inner Rage, those numbers are relatively pointless.

    Quote Originally Posted by Andenthal
    Was trying to figure out why Revenge was less substantially threat and damage than 1 target Cleave and just barely more than Devastate.
    I think you forgot to include the changes to Revenge's AP scaling that occured in the 3.3.3 patch. Revenge should now have a 31.05% AP coef. (50% increase over the old 20.7%).
    Thank god, I knew there was something there, but just couldn't figure out what :P

    Edit:
    I think it's safe to say that Bastion of Defense will not have a 100% uptime (the +dmg component), as it's a 20% chance on a Dodge/Parry/Block. Even when reaching unhittable, it will not have 100% uptime (~74%). I'm not sure how you want to calculate that for the DMG_MOD cell, but a flat 10% increase to damage doesn't seem correct.
    I will add an uptime model for it now.
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  10. #10
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    WarTotem, check your private messages
    Failure is not an option. It comes bundled with the software.

  11. #11
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336
    Checked, fixed, replied and updated.
    It should now have all reported issues fixed.
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  12. #12
    Join Date
    May 2009
    Posts
    167
    The Rend damage formula seems off (compared to Live at least) and doesn't match the ones we saw in the patch notes for build 13241 (http://www.mmo-champion.com/content/...ta-Build-13241)
    Rend base damage has been reduced by 17%, from 635 to 528. Additional bleed damage increased by 25%, from [ 8.57% of MWS * AP + 60% of MWB + 60% of mwb ] over 15 sec to [ 10.71% of MWS * AP + 75% of MWB + 75% of mwb ].
    I haven't been able to figure out what MWS, MWB and mwb mean.

    (speculation follows)

    MWS is probably base weapon speed. MWB and mwb are perhaps max and min weapon damage?
    Last edited by Stengel; 11-13-2010 at 10:47 AM.

  13. #13
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336
    Assumptions are correct, formula is just written differently, because I use http://cata.wowhead.com/spell=94009 and modifiy to reduce references.
    Note that 10,71% = 1,5 / 14 and 75% * (MWB + mwb) = 1,5 * avg weapon damage (which is weapon DPS * speed)
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  14. #14
    Join Date
    May 2009
    Posts
    167
    On live my Rend does more damage than what is expected according to the formula. I had the following in my tests (fury spec, def stance)

    Damage per Tick (noncrit) 701
    Attack Power 3984
    Base Weapon Speed 2,6
    Min Weapon Damage (Paperdoll) 1702
    Max Weapon Damage (Paperdoll) 2055
    DPS (Paperdoll) 722,5

    Spreadsheet formula: 528+1,2*(DPS*BASE_WEAPON_SPEED+(3/14)*ATTACK_POWER)

    Which is 528+1,2*(722,5*2,6+(3/14)*3984) = 3806 (634 per tick)

    That is what I wanted to point out. Mistake on my part or incorrect foruma?

  15. #15
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336
    Incorrect scaling in the formula, it should be;
    528+1,5*(722,5*2,6+(2,6/14)*3984) = 4455 (637 per tick, there is 7, not 6)
    This is 91% of what you said, so add in Enrage and you're where you should be!

    Will fix Rend in an upcoming update
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  16. #16
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    "Abilities" tab
    "Total hit damage" rows (all abilities)

    It looks like a formatting error occured when you put the HtL and Bastion of Defense models in.
    It looks like a cell named "BA_RED" (H38 - guessing that should be for boss armor redux) is being used in calculations of damage from abilities. Guessing something happened with adding rows or something for the HtL and BoD uptimes. Renaming H22 to BA_RED fixes it.

    Also - Rend's damage is being calculated with Boss armor redux.

    Edit: not an error really, more of a question. I noticed that Victory Rush doesn't include any of the healing benefit in the threat calcs. Is this becasue of the unpredictable nature of the VR heal?

    Also - to WarTotem, or anyone else that knows for sure; Does Heroic Throw still reset the swing timer?

    If not, it's looking to be just below Revenge for damage done, and below only Shield Slam for threat. With swing reset taken into account, it's still top 3 threat, but drops a bit in damage compared to other abilities (about equal to Devastate).
    Last edited by Andenthal; 11-18-2010 at 01:30 PM.
    Failure is not an option. It comes bundled with the software.

  17. #17
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336
    Quote Originally Posted by Andenthal View Post
    "Abilities" tab
    "Total hit damage" rows (all abilities)

    It looks like a formatting error occured when you put the HtL and Bastion of Defense models in.
    It looks like a cell named "BA_RED" (H38 - guessing that should be for boss armor redux) is being used in calculations of damage from abilities. Guessing something happened with adding rows or something for the HtL and BoD uptimes. Renaming H22 to BA_RED fixes it.

    Also - Rend's damage is being calculated with Boss armor redux.
    Fixed now. This changes a lot, because it makes BnT worth more than Devastate/Revenge pure threat (not counting SnB procs) if you refresh after 6 ticks.

    Edit: not an error really, more of a question. I noticed that Victory Rush doesn't include any of the healing benefit in the threat calcs. Is this becasue of the unpredictable nature of the VR heal?

    Also - to WarTotem, or anyone else that knows for sure; Does Heroic Throw still reset the swing timer?

    If not, it's looking to be just below Revenge for damage done, and below only Shield Slam for threat. With swing reset taken into account, it's still top 3 threat, but drops a bit in damage compared to other abilities (about equal to Devastate).
    I can not predict if VR will be used as result of KB, or due to Impending Victory procs. Not to mention that self-healing threat is spread over all targets you're in combat with, not just the one you hit VR with.

    Don't know about HT for sure, but I don't think it matters much.
    Personally, I'ld say that while static tanking, HT is another "3rd GCD filler" like Shockwave & Conc Blow unless you wish to preserve it for the Gag Order effect. On any fight where there is a need to pick stuff up or run in, HT is best used during that phase, because you're not meleeing anyway (and it's the only thing you can use while at range).
    In both scenarios, the swing timer resetting or not has no effect on how you use it.
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  18. #18
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    I was looking at the difference between a "max TPS" rotation and a "max DPS" rotation.
    (I told you in a message I use these tools differently than most I think).
    If HT resets the swing timer, then the "true" threat and damage would be the ability minus a white swing. The difference is enough to put it above or below other abilities.

    It's interesting that abilities change priorities as your AP increases.
    For example, at about 17250 AP (slightly more if you have Deep Wounds), HS becomes more efficient in terms of DPR and TPR than Devastate does. It only takes about a 60% Vengeance stack to get there (depending on other stats). If you had 30 rage, it would be better to use HS and "waste" 2 GCDs than it would be to do 2x Devastates (assuming you're not interfering with SS/Revenge). You would do the same amount of threat, but more damage.

    In reality, that situation will likely never occur, as something hitting you hard enough to give you 60% Vengeance isn't going to leave you rage starved. What it does mean though, is that it can be more efficient to use some abilities over others with a high Vengeance stack.

    Interesting (at least I think so) things that happen with 20k AP:
    -Heroic Strike is more damage AND threat than anything excpet a Heavy Repercussions buffed SS.
    -Heroic Throw is significantly more threat than Revenge (50% more), at about the same damage, barely below SS.
    -SW and VR are both more damage than SS - although SS is more threat due to threat bonus - although VR's usage is limited, or enabled by a much weaker ability. If you manage to get it to proc on a KB, use it behind SW in priority.

    Had 8% hit and 26 Expertise, 20k AP, and 13.83% buffed Crit (including HtL). Deep Wounds changes the numbers around slightly, but not a whole lot. It's especially interesting for me to see how low Revenge gets with this much AP. For the most part, it's above only Rend and Devastate in both damage and threat, almost turning into a filler ability.
    Failure is not an option. It comes bundled with the software.

  19. #19
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,336
    Did you take into account the weapon damage scaling of Devastate?
    Regardless, I agree with the fact that Devastate still does not scale enough (hasn't since they created in tBC tbh).
    I would also think that Deep Wounds would actually make the threshold for HS lower if you have Incite, not higher.

    On HT: The 'true' threat would actually be the ability minus 50% of a swing, which is the average amount of swing timer you lose.
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Mists of Pandaria Protection Warrior Spreadsheet
    Warlords of Draenor One Minute Field Guides

  20. #20
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    I was using a 2.6 spd weapon with something like 430 base damage (I think whatever you had in there as a default). I figured a slow weapon would favor a more "traditional" rotation, instead of choosing a fast weapon and getting odd-ball results.

    At first I was just playing around with numbers, seeing how much DPS/TPS was gained from Vengeance - a shit-ton (approx.) - how much from Hit/Exp, etc. Basically, if you're expecting to have any Vengeance on you whatsoever, it's hardly worth it to worry about hit or expertise. There is a gain in DPS, but it's pretty minor compared to what you gain from Vengeance. If you're not expecting a lot of Vengeance (5 mans), then Hit/Exp are still your biggest gains in DPS, same as before.

    Some of the other stuff just came as a side effect. Namely the results from HS.

    I liked the new plan they had for the ability when it was first announced, but it doesn't seem to be working as planned - at least in sims (and not from what I've seen of level 85 raid parses either). It's still our #1 damage ability (both per use, and oveall). There's a very, very minor penalty for mashing it too much and running out of rage - while not mashing it frequently enough is penalized with markedly lower DPS numbers.

    Dropping HS completely from your rotation can be anywhere from a 35 - 50% drop in DPS, while completely removing any one of our CORE, TALENTED abilities from the rotation is only 10-15% drop in DPS. Incite is a larger DPS/TPS gain than Improved Revenge and Sword and Board combined. (even being very generous with SnB procs.)

    Let's see what we would have to give up to lose the same amount of DPS that Incite gives.
    3 points in Incite is a larger DPS gain than;
    2 points in Improved Revenge
    3 points in Sword and Board
    Glyph of Revenge
    Glyph of Devastate
    Glyph of Shield Slam

    ...combined

    Incite is about equal to the DPS gain from both Hold the Line and Bastion of Defense.

    I'm just not of the opinion that a Tier1 ability should be able to completely over shadow everything else in the tree by magnitudes. Especially when it's marketed as a "dump" - something we should only use when we have more rage than we can spend. The fact is, using this ability in place of other abilities yields a net gain, not a net loss as it should.

    The difference between a 12k DPS Prot Warrior and a 10k DPS Prot Warrior will still be how many HS he can get off - NOT how well he uses SnB procs or anything else. Using a poor priority system for the entire fight will give you 90% of the max effectiveness of the optimal priority system. Only hitting HS 10 times per minute, instead of 20 times per minute is a 25% drop in effectiveness.

    It seems we are still fairly reliant/dependant upon this ability. The only difference is we are hitting it every 3 secs, instead of every 1.6.

    Quote Originally Posted by WarTotem View Post
    On HT: The 'true' threat would actually be the ability minus 50% of a swing, which is the average amount of swing timer you lose.
    That's a good tid bit, thanks for that.

    Edit:
    Quote Originally Posted by WarTotem View Post
    I would also think that Deep Wounds would actually make the threshold for HS lower if you have Incite, not higher.
    The extra damage from DW is a larger gain (proportionately) for softer hitting abilities than large ones - the DW gain is static, and doesn't scale with the ability.
    Devastate crits for 4k and gets an extra 1k from DW = 25% gain
    HS crits for 8k and gains 1k extra from DW = 12.5% gain

    That's why it takes longer for HS to catch up in TPR with DW.
    Last edited by Andenthal; 11-22-2010 at 06:55 PM.
    Failure is not an option. It comes bundled with the software.

Closed Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts