+ Reply to Thread
Results 1 to 8 of 8

Thread: Warrior threat values spreadsheet

  1. #1
    Join Date
    Jan 2010
    Posts
    47

    Warrior threat values spreadsheet

    1) Intro & interesting results
    2) What it does (and doesn't do!)
    3) How to use it
    a) How to fill it in
    b) Predicted values
    4) Formulae used
    5) Testing
    6) Potential improvements/additions


    Edit: Have updated and uploaded new version, you can now just enter hit rating, crit & expertise (the expertise value not % reduction) as they appear on your character sheet and it will not think you have negative results in the hit table if you are overcapped. I have left crit as a percentage as that is how it appears on the char sheet (I have not actually fully fixed crit so if you put in a huge amount of crit that takes you over the crit cap the results will be wrong but I figure there aren't too many crit capped tanks out there, I'll probably fix this soon. It will not however think you have negative crit any more if you are under 4.8% when buffed so you can enter as small a number as you like now).

    Also added arp rating.

    1) Intro & interesting results:
    Having had some spare time recently, I decided to do some testing and theorycraft on prot warrior threat and damage numbers for various abilities. I developed a spreadsheet mainly for personal use and to gain a better personal understand of the mechanics with a view to updating it when cataclysm hits. I have decided to post it here for feedback and advice (and maybe it will even be useful for a few people!). You can find it attached at the end of this post. I'm aware that there are already similar, more advanced tools out there but it's not intended to replace any of them, it was really just an exercise I did that I'm sharing.

    It initially began as a tool to simply compare Shield slam numbers with revenge with various gear setups and raid compositions, however, as I added quite a few new abilities and changed the format slightly and they way some things are input and function, I thought I'd make a new post as editing the whole of the old one (which was for a slightly different tool) would have been very confusing. The old thread can be found here (minus the old spreadsheets) if you are interested in how it began:
    http://www.tankspot.com/showthread.p...mg-Spreadsheet

    However, please don't post in the old thread as I don't want to have 2 threads running for this.

    I'd just like to say thanks to the people who put in a lot of hard work on threat values and spreadsheets both here and on EJ, it's been great to learn about the game mechanics through their work and others'.

    A few points of note seem to be:
    a) Whether or not shield slam does more threat than revenge is dependent on gear and raid setup. This is simply the amount when used though, not taking into account things like getting more S&B procs when placing shield slam before revenge etc.
    b) Weapon speed does not have a very big impact on devastate damage (and therefore threat) when raid buffed due to normalization of the weapon damage component and the extra damage from sunders also being unaffected by weapon speed.
    c) Shockwave's threat and damage are considerably higher in a raid situation than I had expected.

    N.B. I am not claiming any of these conclusions to be Gospel or earth-shattering new discoveries, they are just what I have observed if this tool is accurate. If you know of evidence to the contrary or reasons why this spreadsheet may be wrong I would be interested to hear about it. I am sure there are much smarter and more veteran theory crafters out there than me!


    2) What it does (and doesn't do!)

    It uses tested formulae for ability damage and threat to give the average threat and damage from one use of that ability (or one swing in the case of auto attacks and heroic strike), all it is is a guideline of how your abilities measure up against each other using various gear setups and raid conditions. The abilities that are currently included are:
    Shield slam (with shield block)
    Shield slam
    Revenge
    Devastate
    Shockwave
    Concussion blow
    Rend* (target >75%)
    Rend (target <75%)

    Auto attack
    Heroic strike

    Deep wounds

    * I saw someone asking about rend somewhere and thought I might as well include it.

    What it doesn't do is tell you what rotation or gear to use, obviously there are lots of things that can affect how you might want to use your abilities beyond the numbers form one swing. I haven't done anything clever with auto attacks or heroic strike vs abilities on GCD, that was beyond what I wanted to do here.


    3) How to use it.
    N.B. This is designed for use on a lvl 83 raid boss, I left the option of toggling target level in in case anyone wants to test it in an instance but all this does is interpolate armour values for lvl 81 and 82 mobs, it does not work out the hit tables for lower level mobs. It assumes the mob is a warrior class/raid boss for the purposes or armour values, I'm not too sure about other types of mob but if you know the armour values and want to change them in the spreadsheet it's possible.

    a) How to fill it in.
    I tried to reformat it to be more user friendly, I hope it makes sense but if anything does not please ask. Everything that needs to be filled in is boxed and it is pre-filled as an example of how it might look when you fill it in. Everything you will likely want to see is on the main page, there is nothing that needs to be entered anywhere else.

    N.B. I haven't tried to work out where all the rounding is done by blizzard so things might be off from your character sheet by 1 here and there, it would require a lot of work and wouldn't make such a small difference to predicted values that I'm almost certainly not going to do it.

    Everything just needs to be entered as you see it on your character sheet, in the case of things that proc (e.g. block glyph) just enter your stats as they appear without the proc.

    Crit - enter as a percentage.

    Hit rating - enter as normal.

    Expertise - enter the value on the front of the character sheet, not the rating or % reduction.

    In all cells it will tell you what values the spreadsheet is expecting, for most buffs/talents there will be a /x e.g. /2 to show that you put in a value of 0 if you don't have it, and 1 or 2 if you do depending on the number of talent points. Others will have a range in brackets e.g. AP buff (548-688) meaning you put in the amount of ap you will get from the buff, enter '0' if you don't have the buff).

    For things you either have or you don't like procs or a armour pot, put a value of 0 if you don't and 1 if you do. The results you will get are simply the values while that proc is in effect, I haven't tried to work out proc rates or uptimes or anything fancy like that.

    The option for 2nd revenge target just halves the innate threat on your main target, needs more testing really, if anyone knows how it works let me know! Just what I observed from one or two swings, the predicted values are still for the main target only.

    Bear in mind when altering talents like vitality that the spreadsheet will assume the stats you enter are correct for whatever you set vitality to. It won't change your 'character sheet' stats if you check or uncheck vitality, the option is there so it can work out your base stats from the ones you have entered.

    b) Predicted values.
    On the bottom half of the main page, the predicted values for an average use of each ability taking into account the hit table for each one as well as deep wounds damage and threat if applicable. It shows raw numbers and the value for each as a percentage of shield slam (without shield block). I've also left a row to show the raw difference between heroic strike and auto attack swings.

    If you want a little more of a break down such as the range of a normal hit for certain abilities, the deep wounds damage and crit values, you can scroll down and see them.

    N.B. I am not sure how the drs for shield slam dmg with high shield block value work, it is not supposed to make any noticable difference unless block value is stacked and I have not seen inaccurate results in my gear.


    4) Formulae used.


    I've tested these and they seem accurate, I get static threat values off by 1-2 but I just used the values provided by previous testers, If you know or believe any of these to be incorrect, please let me know (including the evidence or links to different numbers if you can). In my damage testing, I found that when an ability had a range for damage, the mean was equal to the median and I have used that assumption here. The values below are all median/mean values if applicable.

    D - stance modifiers:
    Threat x 2.075
    Damage x 0.95

    Damage multipliers are multiplicative (tested using shockwave). Perhaps some of them are not, any info would be much appreciated, I only tested t10, stances and talents (excluding imp d-stance talent).

    Shield slam:

    Shield block value:
    Multipliers are additive i.e. Block Value = base value x (1 + sum of multipliers as a decimal) e.g. with shield mastery, block glyph proc and shield block it would be:
    Base value x (1 + 0.3 + 0.1 + 1)

    Damage = 1015 + shield block value
    Threat = (damage + 770) x 1.3

    Revenge:
    Damage = 1817 + (AP x 0.31)
    Threat = Damage + 121 (assumed Damage + 60.5 for 2 targets but didn't do much testing on this, innate threat appeared to be roughly split from a few hits).

    Shockwave:
    Damage = AP x 0.75
    Threat = Damage
    Assumed it cannot be dodged or parried, but can miss.

    Concussion blow:
    Damage = AP x 0.38
    Threat = Damage x 2
    Can be parried, assumed it can be dodged.

    Devastate:
    Damage = {1.2 x [(Weapon dps x weapon speed) + (AP/14 x 2.4)]} + (242 x sunders on target)
    I assumed it works off the sunders already up and doesn't include the ones it applies, if anyone can confirm this or let me know otherwise that would be great.
    Threat = Damage + (315 + 5% AP) unglyphed
    = Damage + (630 + 10% AP) glyphed
    The number of sunders on the mob does not appear to affect threat.

    Rend:
    Damage = 380 + [(Weapon dps + AP/14) x weapon speed)]
    x 1.35 if target over 75% hp
    Threat = damage

    Auto attacks:
    Damage = [(Weapon dps + AP/14) x weapon speed]
    Assumed 24% chance of glancing blow
    Threat = damage

    Heroic strike:
    Damage = Auto attack dmg + 495
    Threat = Damage + 259

    Deep wounds:
    Damage = (Weapon dps + AP/14) x weapon speed x 0.16 x talent points in deep wounds
    Threat = damage

    I may have made a mistake copying these from the spreadsheet and my notes. I have tested them all with various gear setups and believe them to be correct, however, if you believe any of them to be incorrect please let me know.


    5) Testing

    I won't go into a huge amount of detail on this, there may be better ways of doing this but here's what I did. Any tips would be much appreciated.

    Damage - done on the heroic training dummy, basically involves using different gear sets and bashing away on it for ages to get numbers in recount. I won't put any specifics in here but ask away if you like. Revenge tested on lvl 81 warder mobs in heroic nexus along with retesting a few other abilities to check armour value was roughly correct from interpolation.

    Threat - Mostly done in nexus and heroic nexus on warder mobs.
    Thanks to Kordam for figuring out that random ticks I was getting previously were due to rage gen from shield spec, it helped immensely.

    Basically, set the omen to 'use focus target' and set the warder to the focus target. Pull it to the crystal spider critter, autoattack the critter and use a focus + stopattack macro for the desired ability on the warder. Note the threat from omen and the warder damage in recount (should just be one ability) and verify the proposed formulae.

    I used both:
    http://www.tankspot.com/showthread.p...-%28Warrior%29
    and:
    http://www.tankspot.com/showthread.php?53864-3.2-Threat-Values-%28Devastate%29

    as sources so a huge thanks to both of you for your hard work.


    6) Potential improvements/additions.

    There are a few very small errors due to rounding (I'm not sure quite what gets rounded and where in blizzards calculations, if indeed anything does) within some of the formulae, nothing that will make any noticeable difference to the results, I probably won't make a huge effort to correct these.

    Shield slam dr for high block values needs investigating and adding.

    Mechanics for blocked hits and crits need adding, I'm not sure how these work but they could be tested on the dummy. They don't appear to have a huge impact on the numbers overall though.

    Need to add a few buffs such as 8% kings and food buffs.

    I can add bloodthirst, mortal strike and sunder as well as more talents for arms and fury warriors who may be tanking at some point.

    Need to add thunderclap and cleave.

    I could add various ways of comparing changes to gear and buffs if it is something people might be interested in.

    Currently it's pretty much at the limit for .xls file size though so I'd have to work out how to zip it or whatever, I'm not that pro with computers sadly


    Anyhoo, I hope this is of some interest to people, feedback and advice would be much appreciated. Cheers, Del.
    Last edited by Delmonte; 07-28-2010 at 05:53 AM.

  2. #2
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    Cool spreadsheet, I plan on messing with this a bit when I have some free time to test out different things.
    Couple things to note for accuracy:

    Your character sheet will incorporate talents such as Vitality correctly. It looks like you are taking the value of STR according to the character sheet, then multiplying by the Vitality bonus a second time. Whatever values are shown on your character sheet for STR/Agil/Stam all ready take talent multipliers into effect.

    Improved Mark of the Wild applies to the Druid only - and is independant of the buff itself. Warriors (any non-Druid really) can never have this bonus.

    Neither of these will likely make a huge difference, but it's still important for accuracy.

    Also, something doesn't "seem" right in some of the damage calcs. According to this, my Shockwaves and Revenges should be hitting for ~16k with everything up on a raid boss (and 30% ICC buff). I'm not sure I've ever seen a 14.5k Shield Slam either. I'll double check my stats and see if I did something wrong on my end.
    Failure is not an option. It comes bundled with the software.

  3. #3
    Join Date
    Jan 2010
    Posts
    47
    Thanks for the feedback, I'll have a check into the vitality again. It shouldn't be multiplying twice, I think I checked it was working with buffs both with and without vitality but perhaps it's not quite right. What it does it to take the character sheet strength and divide this by the bonus from vitality (Which is the b4/(1+0.02*d21) part in the buffed strength cell) to get the base strength without the talent. It then adds flat buffs to this base and remultiplies the new 'buffed base' by vitality. I think the version I initially uploaded had the error with vitality you're describing but I tried to correct it, have you dl'ed a recent version?

    So basically, it should divide the character sheet by vitality before adding buffs then multiplying once rather than multiplying by vitality twice, might be entered wrong though.

    I didn't realise that motw worked that way, thanks for pointing that out, I'll have a look into that too. I can just take the option for Imp Motw out in that case. Can you just verify for me how imp Motw works, the way it works in my spreadsheet it it adds 37 to your base stats, and if you select 1 or 2 in imp MotW it will add 37 x 1.2, or 37 x 1.4 (which isn't quite spot on I know) to your base stats. It doesn't multiply attributes by 1/2% currently, I assumed that was the only part that just applied to the druid. Does 2/2 imp MotW add 52 stats or just 37 to characters other than the druid?

    On the subject of motw I've forgotten to add in the armour buff from motw, it shouldn't affect results much but it needs changing.

    I think some of the ranges (where buffs are entered as a flat value) given may be incorrect too such as imp dev aura, but it should still work if people enter the correct values, so for example, if you can actually get more than 1808 armor from dev aura, just enter the value you actually get.

    I think you might be looking at the threat values rather than the damage values there, if you scroll down it will give you a breakdown of average hit and crit damage. For my gear which is 4t10, 264 i-level it predicts roughly:
    SS hit damage 3900, crit 8500, average per swing - 4800
    Shockwave hit - 6900, crit 15000, average per swing - 7800
    Revenge hit - 6800, crit 15000, average per swing - 6700

    That's with pretty much every raid buff up so it shouldn't be predicting 14.5k damage shield slams, I get 14.3k threat on average for shield slam predicted though. Which cells do you see these numbers in? I'll see if I can figure out what might be going on.

    The numbers in the table on the first page are an average per swing taking into account hits, misses, crits and deep wounds. Shockwave can't be dodged or parried afaik so that bumps it up a bit on average. I didn't have much chance to test this in a raid setting, it was really all done in instances and on the target dummy. There may well be problems as my guild stopped raiding a while ago so perhaps some of the scaling up has errors in it, what sort of numbers do you normally see for those abilities? Would be really helpful to see some data from raids if anyone has any lying around including the buffs and stats to get a feel for how it's working in ICC.
    Last edited by Delmonte; 08-02-2010 at 02:32 PM.

  4. #4
    Join Date
    Jan 2010
    Posts
    47
    Mm, I quickly checked it just using some MotW drums and it was working correctly for strength with vitality. It was missing the armour bonus from MotW, I'll upload a corrected version when it's fixed (Imp motw may still be incorrect).
    Last edited by Delmonte; 08-02-2010 at 02:42 PM.

  5. #5
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    Quote Originally Posted by Delmonte View Post
    Thanks for the feedback, I'll have a check into the vitality again. It shouldn't be multiplying twice, I think I checked it was working with buffs both with and without vitality but perhaps it's not quite right. What it does it to take the character sheet strength and divide this by the bonus from vitality (Which is the b4/(1+0.02*d21) part in the buffed strength cell) to get the base strength without the talent. It then adds flat buffs to this base and remultiplies the new 'buffed base' by vitality. I think the version I initially uploaded had the error with vitality you're describing but I tried to correct it, have you dl'ed a recent version?
    It's possible I'm using an older version.
    Tryhing to remember where I got it....
    http://www.tankspot.com/showthread.p...mg-Spreadsheet
    Spreadsheet link at the bottom of your OP.
    The file is named "Threat Spreadsheet2.xls"

    So basically, it should divide the character sheet by vitality before adding buffs then multiplying once rather than multiplying by vitality twice, might be entered wrong though.
    No, you're right. I must not have looked at it close enough.

    Can you just verify for me how imp Motw works, the way it works in my spreadsheet it it adds 37 to your base stats, and if you select 1 or 2 in imp MotW it will add 37 x 1.2, or 37 x 1.4 (which isn't quite spot on I know) to your base stats. It doesn't multiply attributes by 1/2% currently, I assumed that was the only part that just applied to the druid. Does 2/2 imp MotW add 52 stats or just 37 to characters other than the druid?
    Pretty sure you've got that right. Yes, with 2/2 Imp MoTW 52 to all stats are added to all players. The Druid just gets 2% to all stats, regardless if the buff is active. (Again, I must have an old version Cell B26 has the option for the buff in my version.) Ok, disregard this part for me failling @ math. .2 = 20% the group buff (not .02 =2%, the personal buff)

    I think some of the ranges (where buffs are entered as a flat value) given may be incorrect too such as imp dev aura, but it should still work if people enter the correct values, so for example, if you can actually get more than 1808 armor from dev aura, just enter the value you actually get.
    It wouldn't be hard to do this within the function. Also, now that you mention it. Any Agility gains you get from buffs would also gain the Toughness bonus. I think that would make cell F7 like this (and I think that takes everything into account)
    Code:
    =B6+D26+B35*3500+((F8-B5)*1.1)*2
    red text toughness bonus

    I think you might be looking at the threat values rather than the damage values there
    I looked at this a 2nd time (all numbers crits with Deep wounds and 30% ICC buff)
    Revenge damage - 16186
    Threat - 33838

    Shocwave Crit - 16276
    Threat - 33774


    Which cells do you see these numbers in?
    F79, F82 for Revenge
    F88, F89 for SW
    (again if this is due to me having an outdated version, then disregard)

    what sort of numbers do you normally see for those abilities? Would be really helpful to see some data from raids if anyone has any lying around including the buffs and stats to get a feel for how it's working in ICC.
    Unfortunately I don't have any logs or anything, most of this is anecdotal - I'm sorry. With the 30% buff I think I was seeing 13.5k SW crits on bosses. Revenge probably around the same.
    Last edited by Andenthal; 08-02-2010 at 04:18 PM.
    Failure is not an option. It comes bundled with the software.

  6. #6
    Join Date
    Oct 2007
    Location
    Portland, OR
    Posts
    435
    OK - I figured it out. When calculating crits with Deep Wounds, you're displaying total damage that will be done by the ability (including deep wound bleed tics) when it crits. I was just thinking of the number that shows up in the combat log when the abillity lands - hence the discrepency.
    Last edited by Andenthal; 08-02-2010 at 04:42 PM.
    Failure is not an option. It comes bundled with the software.

  7. #7
    Join Date
    Jun 2009
    Location
    Belgium
    Posts
    2,251
    Couple of points:
    * Defensive Stance bonus is 207.3% and Battle/Zerk are at 80%.
    * Toughness has no effect on Armor from Agility, as it only works on armor granted by items.
    * Missing Mocking Blow as threat ability

    See the link in my sig, I got it all worked out there.
    Quote Originally Posted by Ion
    Damn old people, screwin' with my grind.
    Protection Warrior Spreadsheet

  8. #8
    Join Date
    Jan 2010
    Posts
    47
    Quote Originally Posted by WarTotem View Post
    Couple of points:
    * Defensive Stance bonus is 207.3% and Battle/Zerk are at 80%.
    * Toughness has no effect on Armor from Agility, as it only works on armor granted by items.
    * Missing Mocking Blow as threat ability

    See the link in my sig, I got it all worked out there.
    Ok thanks, I'll look into the D-stance mod, had a quick search and found 207.35% on one source, can't remember where I got 207.5% from off the top of my head. I was going to initially add the option of selecting stance but it just wasn't something I was interested in and I already needed to cut the size down. In what sort of situations might people be interested to know the prot values for battle or zerker stance? I only ever swap stances to use shatter and then I swap back immediately but maybe I am missing something.

    That was my understanding of toughness and how the spreadsheet functions atm.

    I'm missing a few abilities, the problem atm is I've added some stuff in but it's now too big to upload as an excel file so that was all I had space for. I wasn't aware that mocking blow could be useful in a rotation so I'll look into that. Not sure how fast it will happen, not playing much WoW atm :P

    Out of interest, could you clarify what the 'effective strength' value in your spreadsheet is please? It is possible for it to be below the character sheet values whilst using some buffs with vitality. It appears at first to be the base strength accounting for vitality, however, if you then add 40 strength for example as a buff, the 'effective strength' in your spreadsheet is increased by around 42 which is roughly 40 x 1.06. Afaik if it's base strength, it should only add the flat buff not including vitality (40 not 40 x 1.06) and then remultiply the total base by vitality to get the buffed value. I posted a query about it in your thread a few days ago, many thanks, Del.
    Last edited by Delmonte; 08-03-2010 at 05:02 AM.

+ Reply to 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