Jambo354

Hi, I don't know if it's on purpose but you can't add hit+stamina gems to the spreadsheet (vivid forest emerald etc).

 

Armadeus

Triel,

I don't know how much you have looked at Landsoul's DPS spreadsheet but I would recomend doing the calculation for the black heart the same way he does DPS trinket calculations.

The calculation would be 10/(45+1(x+y)) Where x is the proc chance and y is the swing timer of the boss. This is assuming that this trinket only procs "when melee strikes hit you" some people on wowhead have sugested this procs off of your melee hits as well.

What this formula gives us is uptime / cooldown. 10 seconds is obviously the uptime, 45 seconds is the internal cooldown, then you add 1 second for (on average) how long it will take the trinket to proc again. The advantage of this formula is that we can see how the trinkets AEP are affected by bosses that swing much faster compared to bosses that swing much slower.

 

Flukeworld

Hi, dont know if its an error, or my math is wrong but:

[Greater Inscription of the Gladiator]

Crit immunity = 689 def rating
15 resil = 0.18% crit avoidance, 22 def rating = 0.18% crit avoidance
So, crit immunity with this enchant drops to 689-22=667 def rating.
The excel table shows 0,02% critable by raid boss at that rating, and you get 0% crit chance on 669 def rating.

Are those 2 points missed somewhere or there is some round up?

 

Infuria

Regal gem cuts are not available, either.

 

Alithiel

To make other gem selections available, you can unhide the Gems sheet and add the names of other gems you might want to use to the list in column T.

Make sure you're using an empty cell, and don't accidentally overwrite one of the formulas to show Dragon's Eye cuts only if you're a Jewelcrafter!

 

Gruntle

The resilience to anti-crit conversions were also changed in this patch (nerfed by 15%). Maybe that's what gives you problems (in case you run with a sheet that has this upgraded, the OpenOffice version in the first post has not been updated with this).

15 resilience = 15/94.27 = 0.16% crit avoidance compared to 0.18%. This seems to match your 0.02% difference.

 

Flukeworld

Thanks, seems i omit that change..

So basicly 15 resil is somewere around 19.57 defense rating, or 669 - 670 (depends how they round the numbers).

 

Armadeus

If you add gems and they still are not visible you may need to add the cells containing them to the namelist for gems. In 2007 click on the "formulas" ribbon and the "name list" button. This will give you all of the defined names in the spreadsheet. I think the name you want to edit is gemlist, you can include the extra cells you want to use.

If you are not comfortable doing all this, just make sure you add all the gems you want in the middle of the list on column T and use the right click "insert" to add new cells, this will automatically expand the name list for gemlist.

 

Reforminded

I have found a few more inconsistancies. The stats for Indestructible Plate Girdle are incorrect, and appear to correspond to another item. Perhaps something is wrong with the sorting macro that has assigned incorrect numbers to things?

 

todemax

I listed such a bug in open office last page. Don't know if the excel version of the macro are just translated.

 

Gruntle

How do you get to the conclusion that the sorting macro is broken?

It seems like an error in the stats for Indestructible Plate Girdle. I changed them to the correct values and pressed the sorting button, the new stats were kept after sorting so I don't think there's anything wrong with the macro. You have to be a bit careful if you add new items to the sheet though so if you have done that, it might be the reason.

I've updated the openoffice sheet for 3.2 (have not put in all the items yet though), send me a pm if you want it.

edit: todemax, I checked the problems you described above. I'm not getting that behaviour at all, cell Z7 use the correct cell references, =IF(AND(AD7=0;Y7="");"";IF(AC7-AD7>$Talents.H$2;"Yes";"No")).

 

todemax

EDIT: Posts seems rather redundant now after Gruntle's edit.

I came to the conclusion from reinstalling open office and reproducing the errors. Unfortunately no one seemingly tried to see if it was reproduceable on their systems. It appeared that column B and AE kept proper references when sorted, but Z, AA, AC and AD didn't. Maybe the macro was fixed in the unofficial update, but errors snuck in from the prior OO sorting bug. Or it could be that I was the only one seeing this behaviour in the original version.

 

Armadeus

So after I got [Fervor of the Frostborn] I felt motivated to model it correctly in the spreadsheet. Here is what I did and why I did it in case anyone else wants to use it or integrate it into a release.

First, reference the swing timer of the selected boss Bosses!C13, this has a large impact on how much AEP this item provides.

Next, there is some delay in the first proc, you pop the trinket and you have to wait for the boss to hit you, you want to make this time as small as possible but you don't want to pop the trinket too late and have to wait an entire swing timer. I used a one second delay to be on the safe side, you guys can use less if you want.

If the bosses swing timer is less that 1 second, it does not make sense to use a full second though, in that case I use the swing timer.

Here is what my formula ended up looking like...

=IF(A14,(((Bosses!C13/120)*1265)+((Bosses!C13/120)*1265*2)+((Bosses!C13/120)*1265*3)+((Bosses!C13/120)*1265*4)+(((IF(Bosses!C13<1,20-Bosses!C13,19)-(Bosses!C13*4))/120)*1265*5)),0)

This is on the trinkets page, I added a spot for this trinket.

A14 is the logical test to see if this trinket is equipped. I take the bosses swing timer and divide by the cool down to get the uptime of each armor buff. For the fifth and final armor buff I take the total time the trinket is active*, subtract the time it has taken to reach 5 stacks, divide by the cool down, and multiply by the armor bonus for 5 stacks.

It turns out that the default AEP value of 900 was a bit high for most scenarios, on slower swinging bosses (like Hodir) The use ability tends to be worth about 600-700 AEP (Depending on stat weights) on faster hitting bosses (Like AoE trash tanking) the AEP value gets up to around 850.

You could drive the AEP value higher by removing the lag I built in, if you assume that you are able to active the trinket as you are being it it would make the AEP a little higher.




*(This is the IF(Bosses!C13<1,20-Bosses!C13,19) statement with the one second lag I was talking about built in)

 

Triel

thanks for the modeling. The reasoning looks good to me.

I was modelling the other trinkets and I was wondering...

When a trinket gives you +X HP, are those HP increased by Vitality or BoK? I would say no because it's not stamina but I need a confirmation

The Indestructible plate girdle had 20 too much def, corrected.

 

Triel

New release of the spreadsheet online :
- Correction on a few items
- New resilience rating
- On use AEP of trinkets updated
- Stamina + hit gems readded to the list

The black heart proc seems a bit overrated due to the fact that the sheet doesn't model the "elemental" blows such has Hodir Frozen blow, so armor is overrated.

I modeled the Fervor of the Frostborn as Armadeus did (the formula is a bit different but it's the same idea)
For Satrina's Impending Scarab, the HP are converted into stamina (minus vitality and BoK) for their AEP value.



Office 2007: http://theranthil.free.fr/wotlkwarr20090821.xlsm
Office 97-2003: http://theranthil.free.fr/wotlkwarr20090821.xls

 

Armadeus

There are a few other trinkets that have HP increases and are already modeled in the spreasheet, you should just be able to use those formulas.

 

Armadeus

Triel, I don't know if you are taking over development of the sheet now since we haven't heard anything from taliafears in a few months. Anyway for whoever wants to take care of bug fixes I'll post them up here.

The buff for blessing of sanctuary is calculated wrong right now... It still gives the rage bonus and does not have the stam bonus.

The rage bonus can be fixed by changing the "Sanctuary Rage Gen" value in the numbers sheet to 0.

To calculate the stam bonus I just used the same formula that was used in BoK and checked to make sure that BoK was not active (can't get the bonus twice). Anyway I put a 1.1 in the val field for BoS and added the following gode for the stam field for Bos.

 

hefti

thx Triel for the update. Thats what i´m looking for since weeks

The Bonus BAC of The Leviathan's Coil is only 448. That makes the ring a little bit weaker it was befor nerf.

... and I´m missing the 10Exp/15 Stamina gem

 

Triel

Thanks for the correction, the 10Exp/15 Stamina gem is the Guardian's Dreadstone, it appears in my drop down list

I have removed many gems from the list because they were almost never used. If you want to add them back, unhide the sheet called "gems" and add their name with the other one in the column T.
You'd better insert cells in the middle of the list so it will update it automaticaly. If you want to add them at the bottom of the list, open the "name manager" and edit "GemList" to include all the names you want.

 

Gruntle

Have anyone seen any formulas/theorycrafting for the DR of block value with Shield Slam? We know that DR kicks in at 2400 additional BV (BV from strength not included right?) and that it's damage contribution is hardcapped at 2760 BV. Have you tried to model it in your sheet Triel?

Guess a first approximation would be to just put a hard cap at 2400, but that seems a bit crude to me.

 

Thorgrim

There are some one-handed dps weapons that I think might be worth including in the chart vs. the standard tanking offerings - particularly the slow ones now that devastate is 100% of weapon damage. This is anecdotal so take it with all the usual grains of salt, but using [Aledar's Battlestar] vs. [Peacekeeper Blade], my dps is typically a couple hundred points higher (with a corresponding threat increase) with the 'non-tank' weapon. Now, some of that is due to the expertise bonus I get as a dwarf, but since the sword itself has expertise on it and I'm already over the dodge cap, the effect shouldn't be that large.

 

Armadeus

I worked on this for a while yesterday, the math is nasty. Right now the sheet calculates total BV so it would have to be rewritten to use diminishing returns BV for SS and normal BV for everything else (damage shield and damage reduction from BV). Do we know what the cap value is for BV? Using the normal b*c/(c+k*c) formula for calculating diminishing returns if you use 2760 for the cap then when diminishing returns kicks in at 2400 BV your BV should be taken back down to 1300.....

Obviously this is not the way it works. We need an accurate value for c, and hopefully the value of k (.9560) hasn't changed.

We need some tests from someone with more than 2400 block value from gear to see what the correct coefficients of the formula are before we can model this in the tanking spreadsheet.

 

arctus

I have a few questions for Triel updated sheet:

The Dodge and Parry values arnt adjusted in the numbers sheet yet. So i ented 45,2.
But after doing so my Def value was to high (higher than dodge).

How can i change the def value for patch 3.2?

Another question is the parry flag (i know what it does). But is there a list with bosses, that have a parry flag?
Do recieve ulduar hardmodes or coloseum bosses recieve parry haste?

 

Armadeus

Ulduar hardmodes are the same a regular. There is a list of bosses a few pages back with the associated parry flag. We don't know about Colosseum bosses yet, we will have to look at a few combat logs before we know.

The value is actually 45.25018692 for parry and dodge. You also need to update dodge per agi (84.745762711) and base dodge (3.66400)

What do you mean your def value is too high? The weighting is too high or the calculated def rating from gear is too high?

There was no defense change for 3.2 so you should not need to change it, if there is something wrong with your defense values then you may have changed the wrong fields in the numbers spreadsheet.

 

Triel

Can someone give me the exact ratings? I used my stuff to determine dodge/parry rating, the values are slightly different from wowwiki. Slightly too much for the usual rounding error