I posted some stuff about raid ISB a little while ago. Link:
http://elitistjerks.com/f31/t17008-w...31/#post586501
In pseudocode:
User-input fields RaidShdHts, RaidShdDmg and RaidISB (not including yourself)
Elsewhere in the sheet PrsShdHts, PrsISB, PrsShdDps
Output TotISB = 1-(1-(RaidISB+PrsISB)/(RaidShdHts+PrsShdHts))^4
Output TotShdDmg = (RaidShdDmg+PrsShdDps)*(1+0.2*TotISB)
PrsShdHts are number of shadowbolt hits per second, percent time casting shadowbolts divided by effective cast time times hit rate.
*ISB in all cases is the number (not fraction) of ISB procs per second on average. Recoding as a fraction is left as an excersize for the reader >.>.

Hope it helps, and thanks for the hard work.

I don't see how you incorporate the ISB charge consommation, especially by shadow priests, with the above formula.

The way I'm currently trying to do it :
- For each shadow user (SU), user sets basic information on cycle (crit, SB cast time, % time spent casting SB, MB cd for SP, etc.)
- With this information, we can compute a personnal ISB uptime for each SU (obviously 0% for SP), and and a "direct damage frequency", wich acts like a weight for the SU ISB uptime. For the personnal ISB, I have found the "Model 1" [ 1-(1-crit)^(12*(cast_perc/cast_time)) ] to be extremelly accurate (3% error max).
- Then, and I am at this step, I'm trying to find a formula wich can determine the raid ISB from the various ISB personnal uptimes and weights. IE :
Find the F function, if it exists : Raid ISB = F({(ISB1, weight1), (ISB2, weight2), (ISB3, weight3), ...})
The weigthed average (ie Raid ISB = (ISB1*weight1 + ISB*weight2 + ...)/(weight1 + weight2 + ...) ) gives mediocre results.

Given crit, nuke cast time (cast_time), % time spent casting (cast_ratio), individual's nukes per second for shadow user 1 is (nps_su1):

[top] cast_ratio/cast_time

Raid's nps (nps_raid) is just the sum of all SU's NPS.

Shadow user 1's share of nukes (weight1) going into the 4 charges:

nps_su1/nps_raid

weighted_crit = SUMPRODUCT(crit1:critN,weight1:weightN)
Raid ISB = (1-(1-weighted_crit)^4)

This is how I made mine do it... mine is pretty crude tho :P

Bolche: In a situation with more than about 2 shadow casters the 12 second duration almost never comes into play. The reason you're having difficulty combining the statistics is, the amount of time one person's ISB proc is up goes down with the number of shadow casters available, is it both gets consumed more quickly and overriden more often. While the function might exist, I'm personally convinced that it's not going to get much simpler than reverse-extracting personal crit chance from each person's ISB and weight and calculating on that, which of course sort of removes the point of computing personal ISB time in the first place =P. Exponentiation and weighted averages are not commutative or distributive operations.

My formula ignores the 12-second duration, and looks only at the odds that the last four shadow nukes proced ISB, which is 1-(1-x)^4 where x is the chance of any given shadow nuke procing ISB. Most of the rest of the calculations are just coming up with that x, as a function of casting time and crit chance.

Trickykid: Your method and mine are equivalent, if coded correctly.

How exactly can I calculate my Crit%?
346(Critrating)/22.1 + 5%(Devastation) + 3%(Backlash) + % from Intellect?

You probably want 5% crit added which is your basic crit chance (although I'm not 100% sure about this).

What I am sure about is that your crit chance (vs 70) on the character panel is correct. You have to add 5% from devastation yourself. Backlash is already counted, since it counts towards all spells.

Okay, here it is : version 1.19 with Raid ISB Model.

I don't put it in the front page yet, as there may be some bugs, consider it as a beta / release candidate. All the tabs are visible for easier checking. I will hide them back for the "final 1.19" release.

Thanks a lot for your advices, PSGarak and Trickykid.

Please repport any suggestion / mistake / error / bug / mispelling !

Base crit chance is 0%. All your crit is accountable by intellect, critical strike rating, and talent points.

Version 1.19 does not function properly with Open Office (at least for me). 1.18.1 did. I get a bunch of err:508, which means "Missing bracket" in OO.

I think the problem is a function called "OFFSET" (by OO). You use it to determin up to which shadow user you must sum up the values in the "Raid ISB"-tab (for example in cell L12). In OO

SUM(OFFSET(reference; rows; columns; h; w))

should do what you need. It determines the total of the area starting with cell (reference_row + rows), (reference_column + columns) and a height of h rows and a width of w columns. So

SUM(OFFSET(B12;0;0;1;nb_shad_users))

returns the sum of the nb_shad_users columns in row 12 starting with B12.

I get no errors in Excel from 1.19.

Also some small tweaks I've made that could go in a future version (no rush):
-TNS for all DPS stats like Intellect and Mp5. Imp Spirit buff's contribution from spirit is easy enough to figure out on your own but Int and Mp5 are useful in my opinion.
-"TNS" for CSD and 4PT6. I manipulated your item-compare pages to do this for me, but for those who use those for other purposes, they could get split out.

Not true, each classes spell crit formula is: int/80 plus spell crit rating/22.08 + talents + base crit.

Warlocks are special, their formula is (Intellect/82) + (Spell Critical Strike Rating/22.08) + talents + 1.7



Another spreadsheet tweak to the meta gem MSD, if you have any of the instant Shadow Bolt talents, the instant spell will eat Focus, so sometimes a Focus will get wasted if you Shadow bolt.

I have the same problem. For the non computer savvy here , can you fix the spreadsheet for open office and host it?

Yes, It seems Open Office Calc does not supported named tables. I have downloaded Open Office and will look at this asap.
The link seems to work for me.
Anyway, all the previous version are still available at url like :
http://www.leulier.com/spreadsheet/w...ps_v1.18.1.xls
http://www.leulier.com/spreadsheet/w..._dps_v1.17.xls
etc.

getting the same errors with OO.

Using an old version an the equipment compare seems to not work well with spell hit while hit capped to compare items. changing the value of spell hit when comparing items seems to not affect dps. I know that 16% is the max, but i can rearrange my gear to stay at 202 hit by changing gems. Anyway that maybe fixed? perhaps the first column subtracts it from your stats and the second column is added.

If I remember correctly (I'm at work so don't have OpenOffice here), if you set the ISB Model to Custom and the Raid TNS to 0, you should not get error on OO.

For your problem, Dotzor, you can just lower your hit value to something like 14% in the main tab.
Still it may indeed be interesting to add an option to the Item compare tab like "Item 1 already equiped", so that only the differences between the two items are used.

Are there any plans to add trinkets to the spreadsheet? It would be nice to be able to compare different combinations with the Darkmoon Trinket, Skull of Gul'dan, Shrunken Head etc. Your work on the spreadsheet is extremely appreciated!

Would it be possible to include the new Spell Haste buff from 2.4 Patch Notes?

"In 2.4, Spell Haste will reduce the global cool down on spells, down to a minimum of 1 second. It won't apply to melee and ranged abilities though however."

EDIT: Realised the stupidity of my post as there are no values as to how much haste is equal to how much GCD reduction, I should have asked "would it be possible to include this change ONCE the details are finalised".

If it doesn't apply to melee and ranged abilities...

I don't get your point, are you saying it's not a factor for Warlocks because you think casted spells are ranged abilities? If so, you're wrong. Ranged abilities means Bows/Guns/Crossbows, etc.

For example, look at Evasion's tooltip, it says "Dodge chance increased by 50% and chance ranged attacks hit you reduced by 25%" but they aint dodging spells

Yeah, but that says 'attacks' not 'abilities' :P I'm sure they mean for it to affect spells, it's just worded poorly.

I made a alternative version of my spreadsheet wich includes the 2.4 change to haste. Haste is asumed to reduce CGD the same way it reduces cast time.

http://www.leulier.com/spreadsheet/w...dps_v1.20b.xls

Hi, I have a question about the item comparison section in the spreadsheet. If I want to accurately compare lets say 2 belts, do I need to plug in the stats I have not wearing a belt on the main page and then enter the stats for the 2 items on the item comparison page to get accurate results? If this is the case it would be nice to rename the "Item 1" column "Current Item" and have the spreadsheet automatically subtract the stats from this column from the stats on the main page as it is making the comparison to the item in the second column.