Jump to content


Photo

Tanking Spreadsheet


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

#321 Executation

Executation

    Glass Joe

  • Members
  • 19 posts

Posted 01 November 2009 - 07:58 AM

Zorba,

Yes, it was cell AC80 on the stats page. Some how I missed the most recent update you released with the Twin Valkyrs and Anub boss profiles. I also like how expertise is brought back into balance as more of a threat stat than a mitigation one. It seems like bosses that are flagged for parry haste are never ones where extra physical burst is ever an issue for healers.

In earlier postings, people have been asking for spreadsheet formulas that address non-linear weightings for defense capping. I am of the mind set that these formulas require a boss rotation table (which would be unique to every boss) that shows average timing of melee strikes and magic attacks. The speed of attacks from bosses would require detailed parsings to detect average attack rotation timing. But once you get the average rotation of the boss, you could determine what the critical stages of incoming damage are, and how much total damage happens in the shortest amount of time. From those critical stages, determine how much stamina and armor is needed to avoid death before a healer can top you off. After a tank stacks about 2-3 critical stages worth of stamina and armor, the weightings on these stats should drop significantly in favor of avoidance stats. Once again, this is a non-linear formula that could probably be solved with iteration using a boss attack rotation table. Personally, I wouldn't know how to manipulate Excel to get this done, but I know it's possible.

A few non sequitur points:

1. Forget threat, it isn't an issue anymore. If we gear for pure survivability, there's enough threat stats on the gear so we don't have to think about it.

2. Of course, this isn't taking into account the severe avoidance debuff coming in ICC, which is basically making us focus on pure stamina and armor.

My question now, is, since ICC is only a month and a half away, should we start weighting our gear now to get ready for the pure damage absorption that will be needed?

#322 Gruntle

Gruntle

    King Hippo

  • Members
  • 509 posts

Posted 02 November 2009 - 03:55 PM

Maybe it was my sheet you tried Executation? I had the error you described in my blocks/min cell for the worst case calculations. Well, it wasn't anything I introduced, the "error" was there in taliafears sheet to begin with, I guess it's a leftover since TBC. I've had problems finding out why BV and Block rating was so highly valued in my sheet, thanks a lot for posting the solution.

The kind of detailed incoming damage simulation you describe would be nice to have, but I'm not sure it would solve anything. You already use the burst survival statistic to weight for the critical situations, the randomness you would get in a full simulation would just make the output confusing to most users and make the sheet very slow and prone to errors.

I agree that threat is hardly an issue any longer, but it's easy enough to turn it off by setting the weight to 0 in the boss profiles.

The avoidance debuff in ICC doesn't really change anything. Well, it means that the TTL and burst survival statistic becomes more similar. And things change in the absolute sense (i.e. your time-to-live will of course go down compared to no debuff), but I can't see that the debuff will affect the AEP values of different stats.


edit: grayed out the last part, I failed to understand how ICC will affect avoidance vs sta/armor. It will and should have an effect on the AEP values. Should be easy enough to see in the spreadsheet.

#323 Executation

Executation

    Glass Joe

  • Members
  • 19 posts

Posted 03 November 2009 - 07:01 AM

Gruntle,

That's a good point with the ability to change the threat value to 0. I'll have to experiment with that.

The idea I was having with the in-depth boss rotation was to use it to base the non-linear equation on that would devalue stamina and armor in relation to avoidance stats. My reasoning behind having that functionality is based on the idea that having an extra 300-500 health (picked a random low amount) becomes a wasted stat if it isn't enough to survive the boss hit that kills you. Basically, if you were to graph the value of health as I'm describing it, you would see repeating sets of sharp peaks and rounded valleys, with an overall decreasing trend to the graph. I wish I could insert a paint image :) .

I'm theorizing that a boss rotation table would be a necessity in order to figure out how close together the bosses largest attacks occur, be they magic, melee, or a combination (a combination is most likely). We would then need to assume a base amount of time it takes a healer to get you back to full and use that number as the "bracket" on the boss rotation table to find the largest amount of damage that can occur between those topped off periods.

I see an issue with determining the amount of time it takes to go from less than full to full, because it's a factor of healer haste, crit chance, HoTs, and cast times. Maybe we could use a worst case scenario for healing like we do for burst value (i.e. no avoidance) ?

Not to mention any extra little damage that can occur while a healer is moving, or healing someone else for a split second.

Is any of this making sense? It's starting to seem like it would be too complicated to bother with configuring, but maybe that's because I'm just not a spreadsheet wizard. I've seen other spreadsheets used by different classes and specs (notably landsoul's sheet) that uses a great deal of in-depth dps iteration.

Zorba,

Do you see any possibilities of building something like this, or am I in the realm of wishful thinking?

*Edit: Does burst time simply assume that the largest melee hit and the largest magic hit happen simultaneously?

#324 Gruntle

Gruntle

    King Hippo

  • Members
  • 509 posts

Posted 03 November 2009 - 09:00 AM

So I guess you mean that each boss should have a set "rotation" (so not really a swing simulation). That should be possible in the sheet even without using iterations. Iterations is problematic in sheets and should, in my opinion, be avoided when not absolutely necessary. Landsoul uses it in his dps sheet and it is working, but it should be noted that almost all of the problems people have with running his sheet is due to the instability that you get from the recursive references in the sheet. Making such boss rotation schemes will be a lot of work though, very hard for one person to get right.

It would also cause quite a lot of non-continuous jumps in the evolution of AEP for different stats. These jumps and peaks will really only depend on the boss rotation so it will be hard to know if you can trust the AEPs you get out (unless the boss rotation is really simple and can be described perfectly in the sheet). At this point the sheet becomes more of a boss fight tool than a gear evaluation tool. Sorry if I sound negative, I still think the idea is quite nice, but I wonder if it wouldn't be better to look at these things in a full simulator.

The burst value is really the worst case. It assumes no misses/dodges/parries/blocks (and a tripled crit unless crit immune). It also assumes that you get no heals at all. Magic damage is included in the statistic, it is set to double the boss value for the burst.

#325 Executation

Executation

    Glass Joe

  • Members
  • 19 posts

Posted 03 November 2009 - 06:55 PM

Let's face it, AEP already gives misleading results by overvaluing redundant stats like def rating after the cap (i know it gives a small bonus to avoidance).

I am making the same argument for stamina (and armor, which has a cap). How often have you seen PUG raids look at the tanks they have and judge them by their stamina or gear score? Tanks even do it to themselves. "You have more stamina so you should tank the boss" even though the MT only has about 2k more health.

I'm not here to QQ about pug behavior, what I am asking for is a slight paradigm shift in the way we think about stamina. We need a better way to model our need for it.

Also, I think the gear spreadsheet is most effectively used as a gear simulation tool based on the encounter. For example, I can plug in all of my best tanking gear and enchants and gems into the spreadsheet, and change the boss profile to anub'arak with parry haste, and it will tell me that these lower iLevel gloves give me a better gear score because they have expertise. In addition, it tells me that I have a better gear score if I enchant them with expertise over armsman, heavy borean, or reinforcements. Now, I also have some leggings with expertise on them in the bank, and I can move some gear around on the sheet to see if I can use higher iLevel gloves (without expertise on them but keep the expertise enchant). In this case, it tells me that I get a better gear score with the expertise gloves and the expertise enchant over any other combination.

The spreadsheet should not be used to determine what drops you should be rolling on from bosses. Item level (along with keeping an expertise set, hit set, parry set, dodge set, and block set in the bank) should make that decision for you. Then once you have a collection of items in the bank, you can use the spreadsheet to simulate the best gear setup for an encounter.

YES, I do think that a tank's goal needs to be setting up the best gear combination for each boss and bringing along all need pieces to swap for each fight (thanks to the squire pony you even have access to your bank once every 4 hours ;) ).

#326 ZorbaTHut

ZorbaTHut

    Von Kaiser

  • Allied Members
  • 44 posts

Posted 04 November 2009 - 09:28 PM

The idea I was having with the in-depth boss rotation was to use it to base the non-linear equation on that would devalue stamina and armor in relation to avoidance stats. My reasoning behind having that functionality is based on the idea that having an extra 300-500 health (picked a random low amount) becomes a wasted stat if it isn't enough to survive the boss hit that kills you. Basically, if you were to graph the value of health as I'm describing it, you would see repeating sets of sharp peaks and rounded valleys, with an overall decreasing trend to the graph. I wish I could insert a paint image :) .


I think it's not entirely clear that this is a thing that actually happens, though. Boss attacks include a reasonably large random factor, the various "multiple attacks" hit at unpredictable intervals, and healing isn't always "up to full". A dude with 29k HP isn't going to survive two 15k hits any better than a guy with 16k HP, but he is going to survive getting hit, getting a single tick of Lifebloom, and then getting hit again.

Essentially, I think that while there may be some value in modeling this, you're essentially asking to model not only the tank, but the boss in greater detail than anyone even really understands, and every single healer that may touch the tank in any way. (In the 29k HP case, a druid is arguably more valuable than a paladin, since the chance of getting a single tick of anything is vastly higher than getting a paladin heal landing.)

As for adjusting weights based on the user's gear, I agree there's some stuff required there, but I'm mostly just leaning on the user's ability to adjust themselves. Those numbers vary dramatically based on the user's guild makeup and progression and there is just no way I can try to instrument it. That's why I moved the "custom boss" interface into the setup page ;)

Let's face it, AEP already gives misleading results by overvaluing redundant stats like def rating after the cap (i know it gives a small bonus to avoidance).


Defense is actually an avoidance stat which fights with dodge for being the best. It's not a small bonus, it's a very, very large bonus.

The spreadsheet should not be used to determine what drops you should be rolling on from bosses. Item level (along with keeping an expertise set, hit set, parry set, dodge set, and block set in the bank) should make that decision for you.).


I used to use the spreadsheet to determine how much DKP to bid on certain slots :D That's actually why I added the delta-EP column - now I can just say things like "hmm I think I will bid one DKP per delta-EP at most."

#327 Executation

Executation

    Glass Joe

  • Members
  • 19 posts

Posted 11 November 2009 - 08:45 PM

*Deleted

#328 Executation

Executation

    Glass Joe

  • Members
  • 19 posts

Posted 16 November 2009 - 03:00 PM

Are you up for another round of wishful thinking? :)

Wouldn't it be nice if we could add a major piece of functionality to the spreadsheet in the way of calculating the best gear setup based on the pieces we tell it we have available?

First off, you would need to be able to use "check boxes" next to each gear piece on the gear list page. Then a user would select every gear piece that they currently have in stock. Then they choose which boss profile they are optimizing for. Click a button that engages a macro which uses all possible gear, gem, and enchant possibilities and then returns with the setup that has the highest gear AEP.

Think how easy it would be to tweak the boss profiles a little bit and see how differently that would effect your optimal gear setup. My mind drools at the thought.

#329 janaka

janaka

    Von Kaiser

  • Members
  • 43 posts

Posted 16 November 2009 - 04:48 PM

Are you up for another round of wishful thinking? :)

Wouldn't it be nice if we could add a major piece of functionality to the spreadsheet in the way of calculating the best gear setup based on the pieces we tell it we have available?

First off, you would need to be able to use "check boxes" next to each gear piece on the gear list page. Then a user would select every gear piece that they currently have in stock. Then they choose which boss profile they are optimizing for. Click a button that engages a macro which uses all possible gear, gem, and enchant possibilities and then returns with the setup that has the highest gear AEP.

Think how easy it would be to tweak the boss profiles a little bit and see how differently that would effect your optimal gear setup. My mind drools at the thought.


You're essentially asking for Rawr here. ;)

#330 Bersi

Bersi

    Von Kaiser

  • Members
  • 26 posts

Posted 16 November 2009 - 06:53 PM

Although it would be nice if Rawr had already 2-3 Bossprofiles included or another easy way to adjust gear ratings (like Icehowl HM or Anub HM Add tanking).

#331 Executation

Executation

    Glass Joe

  • Members
  • 19 posts

Posted 16 November 2009 - 07:27 PM

Think about how easy it would be to min/max a different setup for each raid boss, carry in all your gear with you, and swap out in between bosses. For instance, some bosses parry haste... others do not. But over here, this guy swings really fast but not that hard, so you want to stack BV.

Plus, depending on how bursty you feel a fight is, you can up the weighting a little and see how that changes your optimal setup.

I guess if what I'm suggesting has strong elements of Rawr, then maybe what we need is a way to combine the two tools, Rawr and this Tanking Spreadsheet.

On the issue of the number of boss profiles, I have been adding profiles for every boss, 10, 25 and hard mode versions. It's very simple to plug in new bosses, simply insert a new line. The setup page picks up the new entry, allowing you to essentially save new profiles.

#332 BWarner

BWarner

    Piston Honda

  • Members
  • 128 posts

Posted 17 November 2009 - 08:12 AM

<aside> The eggheads over at Rawr are creating and beginning to implement a method to define fight variables, such as movement. This is still in the active development stage, so feel free to chime in over in the Discussions page of Rawr with more features that you wish to see in regards to this, especially once we get some more tanking-specific development in this feature. </aside>

Regardless, more on the subject of spreadsheets, optimizing a gearset isn't exactly macroable. Given the sheer exponential possible setups in regards to gear, enchants, gemmings, talent specs, etc., this really isn't a feasible avenue for a spreadsheet to pursue. I won't say that the concept isn't worth it (as evidenced by the Rawr implementation, it is totally doable, and very much "worth it"), but it's really just not something that lends itself to a spreadsheet.

In regards to having one tool support the other, I think it would be totally worth allowing imports/exports of CSV files that would store character setups, so as to provide more portable testing and comparisons between the two tools.
The Warrior Formerly Known as Aerowyn.

#333 ZorbaTHut

ZorbaTHut

    Von Kaiser

  • Allied Members
  • 44 posts

Posted 17 November 2009 - 09:07 AM

Regardless, more on the subject of spreadsheets, optimizing a gearset isn't exactly macroable. Given the sheer exponential possible setups in regards to gear, enchants, gemmings, talent specs, etc., this really isn't a feasible avenue for a spreadsheet to pursue. I won't say that the concept isn't worth it (as evidenced by the Rawr implementation, it is totally doable, and very much "worth it"), but it's really just not something that lends itself to a spreadsheet.


I actually don't think it's anywhere near as bad as you think. Yes, technically the possibilities are exponential, but back in reality, there isn't a single case where the Saronite Defender is going to beat out the Crystal Plated Vanguard. There's going to be maaaaaybe five viable options for the most contested slots, and in most slots you're going to be looking at one or two viable options. And that's assuming you have every single drop in the game. On top of that, there are very few practical hard discontinuities in warrior tanking - in fact, the only two are the hard expertise cap, the hit cap, and the block set, and you're not really likely to be hitting those in anything besides a top-end gimmick set.

This is one of those cases where a few good heuristics would 99% solve the problem.

On the other hand I have no interest in actually coding it in OO's wonky programming language.

#334 ThresherInc

ThresherInc

    Glass Joe

  • Members
  • 10 posts

Posted 07 December 2009 - 11:23 AM

Is this sheet still under development?

I notice a number of missing items.

I'm happy to have a go at updating it myself but don't want to tread on any toes.

:D

#335 Damgaard

Damgaard

    Glass Joe

  • Members
  • 3 posts

Posted 08 December 2009 - 02:07 PM

Great spreadsheet Zorba.

I was a tad bored at work today so decided to add all the 5 man and emblem items that has already been found. As well as the loot from the first 4 bosses in Icecrown citadel 10/25 on normal difficulty. More so we have something to go with till your revamped sheet is ready, hope you take no offense.

The updated file can be pulled from here
A list of items added can be seen here

Will try and update as more items are being released if no one else beats me to it.

I was not sure on how to model the proc for Unidentifiable Organ. It's a question on how often it falls off. In rawr its modelled based on the boss attack speed but this is not implemented here yet. Guess we could make a lookup to the chosen Boss Profile and the associated attack speed. Anything below or at 1.8 ought to keep it active most of the time. For now it is modelled with a 80% uptime of a full stack

Still haven't found out how easiest to take into account the lowering of dodge when entering Icecrown. Any suggestions?

#336 Speireis

Speireis

    Glass Joe

  • Members
  • 1 posts

Posted 10 December 2009 - 07:36 PM

Regarding the theckhd did some great math already on maintankadin:
For the sake of clarity i'll just post the conclusion, for more in-depth analysis please follow the link.

Running this for 10^7 iterations with 45% avoidance (p=0.55*0.6=0.33 [probability of a proc]), which takes about a minute, gives me 80% uptime and a time-averaged stack count ( mean of the current stack size ) hovering around 4. I also ran it for a few other values of avoidance to see the scaling:

Avoidance    p    uptime    mean stack size
40        0.36     84.5      5
45        0.33     80        4
50        0.30     77.5      3.2
60        0.24     68        2

Keep in mind that these values are somewhat fungible, since even with 10^7 iterations we see +/- 1% uptime and about 0.1 stack size.

Not surprisingly, getting hit more raises both the uptime and the mean effectiveness of the trinket, and getting hit less reduces its effectiveness. Unfortunately, this is sort of counter-intuitive; as your gear improves and your avoidance increases, the trinket gets progressively worse.

Even in the best case scenario of an average of 5-stacks, this is only an average of 135 stamina, which is quite weak. It could be a good bit stronger for tanking multiple mobs, or for bosses that attack more quickly. For example, using 45% avoidance and a 2.0 boss attack speed boosts the uptime to 86.6% and the mean stack size to 6.4.

We'll have to see what the attack speed of a normal ICC boss is to determine exactly how effective we expect this to be.



#337 ZorbaTHut

ZorbaTHut

    Von Kaiser

  • Allied Members
  • 44 posts

Posted 12 December 2009 - 05:58 AM

I was a tad bored at work today so decided to add all the 5 man and emblem items that has already been found. As well as the loot from the first 4 bosses in Icecrown citadel 10/25 on normal difficulty. More so we have something to go with till your revamped sheet is ready, hope you take no offense.


I love you forever. :D

I've actually been procrastinating on this just because I don't want to do the data entry - I've got a version with the dodge debuff included already, I just don't have the files. Unfortunately, your updated-items link appears to be broken, I can't download it. Is there any way you can repost it? I looked through the updated-file version and I just know I'm going to miss stuff if I try scraping that manually.

I'd really be quite happy if you were willing to do the data entry parts for me ;)

I'm putting together a new version right now with the Damage Shield->Deep Wounds thing changed (and I just found a bug causing Deep Wounds to not adjust properly based on hypothetical stat changes, sigh) so I'll post that soon.

#338 ZorbaTHut

ZorbaTHut

    Von Kaiser

  • Allied Members
  • 44 posts

Posted 12 December 2009 - 06:19 AM

Okay, here we have it.

3.3-Compatible Sheet

No 3.3 equipment is currently included. I don't have any real numbers for bosses, so I made some stuff up to test the Icecrown debuff. I'd love real numbers, and I'd really appreciate it if someone did the data entry bits (please leave them in a single place so I can copy-paste into my sheet easily, don't sort the sheet or it makes it much harder to re-integrate.)

I'm planning to start a new thread for the spreadsheet once I get the 3.3 gear in. If nobody steps up, I'll do it eventually, but I'm kind of crunched on time :)

#339 Damgaard

Damgaard

    Glass Joe

  • Members
  • 3 posts

Posted 14 December 2009 - 02:26 PM

Have added all the currently found normal loot in your 3.3 version. They are inserted with an empty row before them after each current list for that type. Futhermore the full list of new items is also added on a new sheet after the proc sheet (remember to insert the new procs as well).
Uploaded here
Edit:Updated link as it was an old version

Missing data:
Proc rate for the revered and exalted rings for Ashen Verdict
Gem stat for Veincrusher Gauntlets
Set bonus for T10 (are they factored in?)

#340 ZorbaTHut

ZorbaTHut

    Von Kaiser

  • Allied Members
  • 44 posts

Posted 15 December 2009 - 04:14 AM

New version, new thread. Let's let this one die so I can update the OP in the new one.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users