Modelling Searing Flame
June 30, 2010 5 Comments
Since I’m trying to be a bit more… approachable & social with this new blog, I thought I’d run you through how I do some of the things I do. The first of which is a little run-through of how I ended up modelling the Searing Flame dot effect.
To follow along, I’ve uploaded the spreadsheet into google-docs. It’s set to View only, but you can make your own copy to play with by going into File -> Make a Copy.
Firstly, you need to figure out your base value set. These are the constants that you’ll use in your calculations, and I prefer to have my constants in cells of their own, so I can tweak them later on. Hit Time is the “attack speed” of the totem, which is 2 seconds. The Confidence value is a stats term, which basically means that I’m looking for something that will take into account 95% of the data samples (for more information on this, go read the Wikipedia entry on Standard Deviations). The Time figure is in minutes, and the figure next to it in C3 is simply a time*60/B1 calculation to give me the number of hits in this time frame. Lasty, Damage is an easy one, just the average damage per hit.
So now that we have our base values, we need to move on to the variables. With models like this, having multiple variables in one calculation set can make things difficult, so for data sets that are easier to understand, stick with one. I prefer to list my variable in the first column, both as a label and as a value to refer to as I go on.
The first thing I needed to work out was how many hits it would take to build a 5 stack of the dot. This is where the Confidence value comes in. I can be 95% confident that a single charge will be applied every 4.75 hits (0.95/0.2). Since we need 5 applications to reach a full stack, we multiply that by 5 to get 23.75. Obviously we can’t have .75 of a hit, so rounding it up to the nearest whole numbers gives us a nice round 24.
Cell B8: =ROUNDUP(($B$2/A8)*5,0)
The ROUNDUP(value,decimal point) function allows you to round values up to a number of decimal points. Likewise, ROUNDDOWN, or just simply ROUND do the same thing, but either round down, or to the nearest whole number in either direction.
The dollar signs in a cell reference ($B$2) means that even if I do a drag fill (clicking the lower right corner of a cell selection and dragging it down), the cell reference will always be pointing to B2. If I don’t do this the next cell down will refer to B3, and so forth (becoming a relative reference, rather than an absolute).
Beside that I added the chance for the dot to drop. Searing Totem attacks every 2 seconds. This means that there will be 7 chances to apply the dot in a 15 second window. Using Probability, we can see that 1 talent point is a 20% chance, so we have an 80% chance of not applying the DoT (1-0.2=0.8). So this means we have 0.8 x 0.8 x 0.8 x 0.8 x 0.8 x 0.8 x 0.8 as the chance the dot isn’t applied and/or is dropped. This gets simplified to 0.8 to the power of 7, or 0.8^7 in excel speak. This gives us a 20.97% that your dot stack will drop, or a 79.03% chance that your dot stack will stay up.
Column D uses both of these values to calculate a “Drop Frequency”, or the number of times the stack will drop during our set time frame. We take our number of hits, multiplied by the chance the DoT will drop off to see how many times during our encounter the stack will drop off.
From here we can work out how many “buildup” attacks there will be, where the stack hasn’t reached 5. This is important for working out how much time we actually have a 5 stack up (it may seem funny, but it is often best to work out how often something doesn’t happen, then invert it using 1-X). For 20%, since it we’re expecting 24 hits at most to build up a 5 stack, and we’re seeing it drop at least 31 times, we can’t reasonably expect a 5 stack to occur. It might, on average we wouldn’t expect it to be.
The MIN command allows us to select the smallest value in a number of values. Both it and the MAX command are useful for setting lower or upper bounds on what a formula will generate. I use it here to limit the build up attack count to be either the calculated value, or the total number of attacks for our fight, whichever is smaller.
Calculating the time at a 5 stack, is as simple as taking the total number of attacks, and subtracting off the build up attack count (this is why we use the MIN formula, to prevent negative values), and multiply it by the attack speed. The time not as a 5 stack is then the total time minus the time at a 5 stack.
The total DPS at the end is a 4 part calculation. We take the time at a 5 stack, multiply by damage * 5, and add the number of attacks times the base damage figure. We then add on the damage from not having a 5 stack, which we can assume as being 2 times the base damage (2 being the average of 0, 1 ,2, 3 and 4, which will only be an approximation, but to be more accurate will require Simulations, more on that later). We take all three damage values, add them together, and then divide by the total time (multiplied out by 60 to give the number of seconds) to generate a DPS figure.
The last two columns allow me to see how much of a DPS gain there is for each variable, and then see how the variables compare to each other. For Gain over Base, it’s just a case of dividing the resulting DPS by the DPS of a Searing Flame-less totem, and subtracting 1 (this gives 26% rather than 126%). For the Gain % of 5/5, I do the opposite, and divide the calculated DPS figure by that of the highest value (being 5 talent points). This means I can see that 3 points, (60% or 0.6) gives me 97.96% of the gain I’d see from having 5 talent points.
And thus, ladies and gentlemen, is roughly how I worked it out. Of course, I wrote this after figuring out the whole thing, so it looks a little bit easier than it was. In fact, I think it has taken me longer to write up this blog entry than it took me to create the spreadsheet (although I did end up re-working the Drop Freq mid-write). Funny how that works.