# Thread: The Cataclystic Protection Warrior Spreadsheet

1. New Registrant
Join Date
Dec 2010
Posts
12
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.

Join Date
Oct 2007
Location
Portland, OR
Posts
435
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.

3. New Registrant
Join Date
Dec 2010
Posts
12
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.
Last edited by tanis; 12-29-2010 at 06:03 PM.

Join Date
Oct 2007
Location
Portland, OR
Posts
435
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.

5. Son of Megatron
Join Date
Jun 2009
Location
Belgium
Posts
2,336
* 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.
Last edited by Airowird; 12-30-2010 at 03:36 AM.

Join Date
Oct 2007
Location
Portland, OR
Posts
435
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.

7. New Registrant
Join Date
Aug 2009
Posts
2
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.

Join Date
Oct 2007
Location
Portland, OR
Posts
435
Originally Posted by Armadeus
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.

9. Son of Megatron
Join Date
Jun 2009
Location
Belgium
Posts
2,336
Originally Posted by Armadeus
...
((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.

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)

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.

10. New Registrant
Join Date
Aug 2009
Posts
2
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.
Last edited by Armadeus; 02-08-2011 at 06:45 AM.

11. Son of Megatron
Join Date
Jun 2009
Location
Belgium
Posts
2,336
Updated for v4.0.6

Let me know if I missed anything

12. 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?

13. Son of Megatron
Join Date
Jun 2009
Location
Belgium
Posts
2,336
I'll check it again this weekend

14. 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.

15. Son of Megatron
Join Date
Jun 2009
Location
Belgium
Posts
2,336
Yeah, been thinking about that as well, will see what I can do to put it in the current spreadsheet

16. 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.

17. Son of Megatron
Join Date
Jun 2009
Location
Belgium
Posts
2,336
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.

18. 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
Last edited by Tengenstein; 02-23-2011 at 09:13 AM. Reason: Bloody table formating. I give up.

19. Son of Megatron
Join Date
Jun 2009
Location
Belgium
Posts
2,336
If BnT5 > SS, shouldn't you always use BnT first?

20. 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.