Obviously I'm doing something wrong but to date EVERY version of the spreadsheet I've tried in the past has bugged on me with an error complaining about circular references. Regardless of if I choose to leave it as or not, the DPS value (A2) on the Gear_buffs tab never changes when I change gear (even using the preset ones). The only error in a cell I see is "=DPS!#REF!" in A1 of the TNS-sdmg tab so was curious if this was the culprit and what should actually be in there?

edit

After seem poking around the circular reference seems to be between DPS!$F$27, DPS!$F$30, DPS!$D$30 and DPS!$J$27.

Using the search, I wasn't able to find an answer for this, but I want to check:

When updating the spreadsheet, if I pull my gear set from the Item_sets tab of the old one and put it in the new, that won't effect any of the "real" data, correct?

Posts earlier in this thread will answer your question: set your program to allow circular references, they're used to determine cast ratios.

Correct, you can just copy/paste and it should line up.

How can one change the formulas inside the spreadsheet without breaking it? I double click a field and the underlying formula is exposed but when I try and change a number the spreadsheet breaks.

(If you are curious, I am trying to test the hypothetical impact of haste effecting dots)

I was trying to find something in options to disable it myself but the previous post you mentioned helped fix it, thank you

http://elitistjerks.com/448663-post223.html

Something in the Advanced info tab screws it up -- if you don't use any items that use advanced info (band of eternal sage, quag's eye, etc), it shouldn't create errors. EDIT: if you change these items out, change the time of the spell then change the item back in, it works... it's only when the item is currently worn and you change the value.

Alternatively you can use a negative value for dot-gap.

Going through these pages I've noticed alot of fancy shmancy formulas for deriving information on hit %s and miss %s.

What I fail to see is how someone on an earlier page listed some formula to show that with 1000 casts of a spell, to expect 17 misses.

This is false. That formula is backwards. What I mean by this, is that you don't use casts versus hits and misses to determine your spell hit rate. Your gear does that. If you have a 17% resist (or miss) chance, that doesn't mean expect your next 983 of 1000 casts to be unresisted. That's insane.

It simply means you have a 17% chance to miss any and every cast you do.

Whether it is 1 cast or 1000.

I'm sure I'll get a lot of arguments and tons of formulae to show differently, or how there are so many other compenrnts in place to show resists, partials, hits, crits and whatever else based on 20 million different modifiers.

But in the end it won't matter. Your hit rating through gear is turned into a percentage based on a mob's 1% innate chance to resist and also uses a level modifier to determine a base value as a "check".

Also this post is a lot of crap because I'm not all scientific, but still, it was driving me nuts to see people use X amount of casts to determine a % for the player. It's not like the mob has a checklist or his own spreadsheet that says "damn, I have not resisted the last 500 casts of shadowbolts and only have 12% life left. I bet I resist the next 56 of them.".

Even with 99% chance to hit you can resist 900/1000 spells cast. And you'd STILL have a 99% chance to hit!

True but that's not what people mean, they mean that over an infinite amount of casts you will get 99% of the spells in. And the higher the amount of spells you cast the closer you get to this magic number. Of course you get lucky or unlucky but that has no bearing here because that nulifies any kind of theoretical calculations that are simply done by numbers not by what the RNG will serve you.

Add luck into theory and all of a sudden you lose the posibility of any kind of analisys that isn't totally too much math for most to understand and that has no meaningful practical application. You'll also notice that in most fights at 99% hit you will most of the time be very close to that (only 1% chance to miss). Doesn't apply to crit though where chance to not crit is usually around 65-70% so probably much higher variations.

In the end the fancy calculations are right, they're close to what we get in reality once we readjust the numbers to luck and when everything aligns they're pretty much spot on so your gut feeling needs a bit of readjustment.

On top of that, remember stat costs are determined by blizzard and have no obligation to follow any kind of logic (which they sometimes do and sometimes don't).

Cenobyte, it's called statistics and the rule of large numbers (or some other termination if I got it wrong, excuse my english). For example:

When you have 50% miss chance:

Cast 1 spell - either it misses or it hits.

Cast 2 spells - either 2 miss, 2 hit or 1 hits and 1 misses. The chance for the extremes is 25% each and the chance for 1 hit 1 miss is 50%, so you'll just as often find yourself losing damage as you find yourself gaining damage from luck, and 1/2 the time you'll actually get exactly that average spreadsheet value.

Now if we cast 3 spells, we have 1/8 chance to miss or hit all of them, and 3/8 chance to either hit 2 and miss 1 or miss 2 and hit 1. As you can see now we have 6/8 chance to get something that's rather close to the average of 1.5 hits and only 1/4 chance to get something extreme - as well as the chance for actually getting more than expected is the same as the chance to get less than expected.

If you keep casting more and more spells, the chance for you to get far from the average goes down tremendeously, which is why we calculate averages and say that out of 1000 spells, with 17% miss, you will generally miss 170. Granted you could miss a little more or a little less but having your miss count go far from 170 is very unlikely to happen. And that kind of luck will hurt you just as much as it helps you, so the small differences caused by luck don't even matter for gear choices.

In your gear tab, do you have yourself set as an Orc?

Two feature requests:
1) 0-4 allowed Drums of Battle rather than checkbox. Can have up to and including 4 drums for max of 80 "static" haste.
2) "DPS Lost if no SB" shown: Useful for doing fire/shadow comparisons or pointing out how much raid DPS your ISBs are contributing (on the average)

The first is easy to implement. The second just takes a little reworking of the Raid ISB tab, just create a mirrored version of it except replace your SB cast ratio with zero and recalc. Then show (SB_uptime - NoSB_uptime) * (raid_shadow_DPS - personal_DPS) * .2 = DPS-loss.

I don't think there is a +20 Spell Hit Rating food (?).

And I can't check the sheet at the moment, but I don't believe that [Kibler's Bits] has been added, it is applied to the pet so another player food can be used at the same time.

Sorry, you are correct.

That would actually be pretty nice - not only help deciding between fire and shadow without writing down as many numbers, but also tell you how much DPS the raid gains by bringing you over, say, bringing another mage (was going to list more classes but apperantely only mages (and rogues but you won't replace a lock with a rogue due to party issues) don't bring any DPS to the raid aside from their own ).

The multiple drums reminds me, an option for a second heroism would be useful.

Hello, I've been having a little trouble understanding how effective damage for certain stats increases DPS. According to the spreadsheet, the value of haste for me is 1.37 effective damage, which would lead me to believe gemming for haste wherever possible would give the highest dps increase. However, when I select gems for my gear, gemming for damage, either by putting red gems instead of orange, orange instead of yellow, or purple instead of green, resulted in higher DPS.

Could someone clarify why this might be? For reference, I run a pretty standard 21/40 DS/SnF build.

Law of Large Numbers.
For random variables with mean and variance (of any distribution!), then the following is true:
.
In layman's terms, the more samples you average, the more reliable your statistics become. Any and all random variables eventually start looking like normal distributions, with standard devations that drop off as the square root of n.
And, unlike most statistics, this is actually not statistics at all. It's probability, which means it's real math!

In more abstract terms: we don't know how much damage we're actually going to do, but we can make predictions and optimize how much we expect to do. I mean, if I knew I were going to get lucky with the PRNG I might gem out of hit and go for more damage and haste. If I knew I wasn't going to crit I could be more gung-ho with agro. And so on. Since I don't know beforehand, prob-stat is the only way to make informed decisions. And while I'm not guaranteed to miss exactly 17 out of 100 shadowbolts, that assumption is how I can maximize the expectation of my damage without knowing the exact results of the random variables encountered.

This may sound very funny, but my guild occasionally sticks me in a group with a BM hunter, so that we can both get a shadowpriest. Is there any chance of getting ferocious inspiration put into the sheet? I imagine that you would want to include how many stacks of it at what % chance you could see (for multiple hunters and since it's not up 100%).

Yes, but seems like this bonus is not calculated. Anyway, I may use it or not in a battle, so it would be nice to see the difference.

FI increases your damage by 3%. At T6 level I've noticed FI having some extremely high uptime. With that said, I believe you could just multiply your spreadsheet dps by 1.03 to get the upper end estimate of your DPS, and 1.01 for the lower end.

You could technically factor in what FI does for the spriest dps similarly, and plug in his DPS to the sheet for the new mp5 gain and subsequent DPS gain.

In my current getup, I'm over the hit rating (going to gem out of +hit soon). However, under the Gems tab, it still shows a +6dmg/+5hit gem above the +14dmg jewelcrafter gem. This can't be right, for my gear, is the gem tab really trustworthy in the current build?

The base scores use a value for hit rating that assumes you are not capped. There is an 'ignore-hit' score as well.