# The Cataclystic Protection Warrior Spreadsheet

Show 40 post(s) from this thread on one page
Page 3 of 14 First 1 2 3 4 5 13 ... Last
• 12-29-2010, 02:19 PM
tanis
The HtL uptime table for calculating expected HtL duration and the final formula for HtL uptime (F21) use the normal swing timer of the boss rather than the effective timer from 'Damage Reduction'.B4.
• 12-29-2010, 04:00 PM
Andenthal
Quote:

Originally Posted by tanis
Average block is either just wrong or I'm not reducing nearly far enough:
F16 = IF(Stats.E17,0.31,0.3)*(1+C16*0.015+E21/10)

It seems to me like the weighted average should be:

( 1 - HtL_Uptime ) [ 0.3 * ( ( 1 - CritBlockChance ) + 2 * ( CritBlockChance ) ) ]
+ ( HtL_Uptime ) [ 0.3 * ( ( 1 - ( CritBlockChance + 10 ) ) + 2 * ( CritBlockChance + 10 ) ) ]
Where the first line is the weighted average of blocks without HtL
and the second line is the weighted average of blocks with HtL.

This cell calculates - on average - how much damage each block will absorb.
It is accurate if you take it as it is supposed to be - how much your blocks will absorb on average.
• 12-29-2010, 05:49 PM
tanis
Thanks, Andenthal; I see what I did wrong. The "/10" confused me because it seemed like it was undervaluing the crit block from HtL. Now I see that the value of E21 is actually a percent between 0,100 and not a probability formatted as a percent, like I assumed.

Both are correct, but the one in the spreadsheet is far easier than mine. On the way home I realized I could simplify it with an expected crit rate, as WarTotem does.
• 12-29-2010, 06:00 PM
Andenthal
Sure. There's also a fairly uncomlicated way to include SB overflow if that's something you're interested in.

I've hacked my version of the spreadsheet up a bit. It's not because the original was lacking in anyway - it's because I use these tools differently than most I think. I like being able to mess with numbers and such to see what happens.
• 12-30-2010, 03:31 AM
Airowird
* The E15 reference was left over for block rating bonuses, as I didn't know at the time if the block enchant was going to stay block rating or become mastery (I was actually hoping for mastery tbh, but I'll add the cell back in for that single enchant if you like)

* First of all, you are substracting CritBlockChance(CBC) and then adding twice that. KISS also works for math :)
If you do that, you'll get 0.3 * [ ( 1 - HtL) * (1 + CBC) + HtL * (1 + CBC + 10%) ]
= 0.3 * [ ( 1 - HtL) * (1 + CBC) + HtL * (1 + CBC) + HtL *10% ]
= 0.3 * [ 1 * (1 + CBC) + HtL * 10% ]
= 0.3 * ( 1 + CBC + HtL/10 )
The Shield Block mechanic is not added in here, because it is not a 'pre-fight' mechanic, unlike gear/flask/buffs.

Oops, guess I forgot to click "Next page" before replying :)
As for why I don't have it in there Andenthal, see above. I've built the spreadsheet on the idea you first plug in the most static effects (gear, then buffs), see what abilities do, make the most out of it (rotation) and when you kill the boss, see if stuff is an upgrade (or if you wipe, see what useless stats you should reforge). I'm fine with you adding what you want in it, I mainly made the spreadsheet for myself and if I can help & other person in the world with it, it was worth posting it here.
• 12-30-2010, 10:34 AM
Andenthal
Oh, I know. We've talked about it before.
You're right in that it needs to have some level of simplicitiy, otherwise it becomes too complicated for less knowledgeable tanks.
• 01-19-2011, 01:45 PM
I think there is an issue with the HtL formula. We are using:

((1-p)^k)*p chance of getting k swings without a parry followed by a parry.

I'm going to break it down by parts to explain.

((1-p)^k) Statistically this number represents (1-p) chance to NOT parry k number of times.

So what we are calculating is the chance parry and THEN NOT parry 1, 2, 3, 4, and 5 times in a row.

I think the forumla we actually want is:

(1-(1-p)^k)*p. This gives us the chance that we will parry and then parry AGAIN after k number of swings.
• 01-19-2011, 02:46 PM
Andenthal
Quote:

I think the forumla we actually want is:
(1-(1-p)^k)*p. This gives us the chance that we will parry and then parry AGAIN after k number of swings.

This gives us frequency, which is not directly related to uptime. It's easy to see if you plot that on a graph, as it yields a linear relationship between Parry percentage and HtL uptime, where as it should be an exponential relationship.

The calculation in the spreadsheet has been used for ages with other very similar mechanics.
• 01-19-2011, 04:23 PM
Airowird
Quote:

...
((1-p)^k) Statistically this number represents (1-p) chance to NOT parry k number of times.

Correct, this is the chance that HtL will run it's full duration.
Similarly (if you see the rest of the table), the chance to Parry on the next hit = p, on the second swing it is p*(1-p) and on the n-th hit it is p*(1-p)^(n-1). The table in the spreadsheet then uses these chances to multiply with the duration the original HtL buff lasted to come up with an average duration of HtL per Parry.

Quote:

So what we are calculating is the chance parry and THEN NOT parry 1, 2, 3, 4, and 5 times in a row.
Actually, the spreadsheet calculates the chance to NOT parry for x times, THEN a parry (within the 10s of a HtL buff)

Quote:

I think the forumla we actually want is:

(1-(1-p)^k)*p. This gives us the chance that we will parry and then parry AGAIN after k number of swings.
Actually, (1-(1-p)^k) gives you the chance that you DID parry within k swings. What you have as forumla there will tell me the odds that a swing at me will grant HtL, but that the buff won't run it's full duration. Which is not useful for specific uptime, because you don't know when in those 10s your buff is being refreshed and how long the new one will last.

I hope that you(plural) understand the way uptime is calculated a bit better now.
• 02-08-2011, 06:33 AM
Thanks War. I think I was trying to make this too complicated. I was trying to do the math under these conditions:
Code:

```                                             1. The boss swings, do I parry?                                 A. Yes                                                B. No   1a HtL Proced. The Boss swings again, Do I parry?                          1b. Start back at 1.       A. Yes                                          B. No   2a. HtL refreshed, start back at 1a.            2b. Is HtL going to drop before the boss swings again?                                                             A. Yes.                                      B. No                                                       3a Start back at 1.                  3b The boss swings do I parry?                                                                                     A Yes                        B. No                                                                   4a HtL refreshed, start back at 1a.    4b Start back at 2b```
.

While this flow chart would give the total time HtL is up it is overly complicated for what we are trying to calculate.
• 02-16-2011, 04:41 AM
Airowird
Updated for v4.0.6

Let me know if I missed anything :)
• 02-17-2011, 11:24 AM
Tengenstein
On the Abilities tab, I'm not sure its calculating Concussion Blow's average threat (cell H10) correctly: Scaling at 75% AP and a double damage threat modifyer before defensive stance?
• 02-18-2011, 06:08 AM
Airowird
I'll check it again this weekend :)
• 02-23-2011, 12:16 AM
Tengenstein
I'm not sure how much this would appeal to people, or even if its necassary, but i've made a rough addition to your Spreadsheet, for BnT refreshed rends, essentially to give a rough idea about how when you refrsh rend affects it's threat.

what i'm really angling for is a working out when, if ever, to break the 4gcd sequence to refresh a rend.
• 02-23-2011, 12:36 AM
Airowird
Yeah, been thinking about that as well, will see what I can do to put it in the current spreadsheet :)
• 02-23-2011, 03:34 AM
Kojiyama
Well, for a single GCD Rend is higher than most things, even without BnT. So it stands to reason that applying Rend at the same time as one can use Shockwave and the like is an optimal rotation. This generally won't lead to 100% uptime, but it also doesn't interfere with the SnB cycle at all.

I'm not certain it would be worth breaking the SnB cycle to refresh Rend, though, unless you are specifically maintaining the Thunder Clap debuff and therefore you can use that GCD as 'dual-purpose' to mitigate some of the TPS loss of using Thunder Clap.
• 02-23-2011, 06:10 AM
Airowird
Well I'ld have to add in more options then, will probably fiddle a bit with it, see what the best way to present it would be.
• 02-23-2011, 09:11 AM
Tengenstein
Quote:

Originally Posted by Kojiyama
Well, for a single GCD Rend is higher than most things, even without BnT. So it stands to reason that applying Rend at the same time as one can use Shockwave and the like is an optimal rotation. This generally won't lead to 100% uptime, but it also doesn't interfere with the SnB cycle at all.

I'm not certain it would be worth breaking the SnB cycle to refresh Rend, though, unless you are specifically maintaining the Thunder Clap debuff and therefore you can use that GCD as 'dual-purpose' to mitigate some of the TPS loss of using Thunder Clap.

i thought as much but i still had some niggling doubts.

So i totalled up some 5 GCDs combinations threat.
SS,dev,dev,sw,ss 84788.56
SS,dev,dev,sw BnT5 87139.86
SS,dev,dev,Rend,ss 88636.20
ss,dev,dev,bnt5, ss 93418.10
ss,dev,dev,bnt4, ss
89799.63
ss,dev,dev,bnt3, ss
86180.95

which implies to me(though i'm sure i've missed something here) that its not worth delaying SS, and is actually better to refresh Rend a tick early.

SS is 20.7k, dev is 14.3k, SW is 14.5k, rend is 18.4k, BnT5 is 23.1k, BnT4 is 19.5k, and BnT3 is 15.9k

I'm sure i've missed a trick somewhere
• 02-23-2011, 01:28 PM
Airowird
If BnT5 > SS, shouldn't you always use BnT first?
• 02-23-2011, 07:06 PM
Tengenstein
I don't know.

i gues i'm kinda comparing this to how SW used to be more threat than a standard SS, but we still found weaving SS into the fourth GCD delaying it was better threat. I'm hoping someone with better mathmatical skills can help me.
Show 40 post(s) from this thread on one page
Page 3 of 14 First 1 2 3 4 5 13 ... Last