[Elemental] SEIC Spreadsheet - v3 - Page 20

Caelwynar · 10/22/09, 8:22 AM

Originally Posted by Caelwynar

That's certainly possible, but would involve maintaining more detailed information about each trinket somewhere, instead of just a simple formula cell for each. I'm not sure the benefit there would be worth the maintenance effort. The DEP values should show up correctly if you add the stats to your character sheet (though the DPS cell would be thrown off, the new trinket stuff I added doesn't affect the model / DEP calculations at all).

Thought on this a bit, and probably the simplest thing to do is have yet another column in the trinket table, this one only giving the DEP of the trinket's proc or use effect, and have that be the one that gets added to the DPS cells. I've uploaded a new spreadsheet with that style change in it; let me know if that seems better than the last.

Please note that Eye of the Broodmother and Illustration have "proc" values of 0; I figured it would make the most sense to add those 125 / 200 spellpower values to your character stats directly, since they're essentially permanent. For all other trinkets, just enter your stats as seen on your character sheet in-game with the trinkets equipped, and both the stat DEP weights and dps total (with procs figured in) should be closer to accurate.

I also noticed that I had modified cell C4 after downloading Bink's original r20.1 spreadsheet to account for my Alchemy bonus, and mistakenly left that in the version I uploaded on Tuesday. That's fixed in today's version (102209).

SEIC 3.2.2_Pahvel_102209.xls

Zamir · 10/22/09, 12:14 PM

More picking through the spreadsheet. DISCLAIMER: I'm an arts major, so I'm learning this stuff as I go along. Rotation tab, cell K7, calculates average total damage from LB including overload. However the LB overload calculation seems to have no crit chance or crit modifier associated with it. The CL one does so I'm assuming it's just an oversight, and that crit modifiers (=B6+callofthunder) and bonus (1.09) should be added into G8 and H8 (and all other iterations of the same template)? Or is LO calculated somewhere else that I've not discovered yet?

Caelwynar · 10/22/09, 5:49 PM

Originally Posted by Zamir

More picking through the spreadsheet. DISCLAIMER: I'm an arts major, so I'm learning this stuff as I go along. Rotation tab, cell K7, calculates average total damage from LB including overload. However the LB overload calculation seems to have no crit chance or crit modifier associated with it. The CL one does so I'm assuming it's just an oversight, and that crit modifiers (=B6+callofthunder) and bonus (1.09) should be added into G8 and H8 (and all other iterations of the same template)? Or is LO calculated somewhere else that I've not discovered yet?

Your observation looks correct to me, though I'm not quite comfortable changing the theorycrafting formulae without a reference or test data to verify that it's valid.

The non-crit damage for LB's Overload looks too high as well (more than half of LB's non-crit in the rotation tables), because Bink didn't divide the Shamanism bonus by 2 like he did for every other portion of the Overload coefficient.

I don't know if that was intentional and LB overloads really do more than half of a LB in-game, or if it's a mistake in the spreadsheet.

Zamir · 10/22/09, 6:12 PM

LB overload and CL overload, though they only receive half the normal LB/CL coefficient, both receive the full 15% coefficient bonus from shamanism. So the total coefficient for LB overload is 50.71% and for CL overload it's 43.57%..

Also, it's not a big difference, but the values used for the overload's base damage are the parent values divided by 2, though for LBO the average base damage value is actually slightly less. And by "slightly less" I mean "2 damage".

I'm still figuring out how the spreadsheet makes its calculations, so I'm also extremely nervous of making changes. I'm also wary of ending up in a situation where 10 different guys have 15 different self-modified versions released and floating around. The more I do figure out, the more impressed I am at just how much work Bink put into this.

Binkenstein · 10/22/09, 6:14 PM

Originally Posted by Zamir

More picking through the spreadsheet. DISCLAIMER: I'm an arts major, so I'm learning this stuff as I go along. Rotation tab, cell K7, calculates average total damage from LB including overload. However the LB overload calculation seems to have no crit chance or crit modifier associated with it. The CL one does so I'm assuming it's just an oversight, and that crit modifiers (=B6+callofthunder) and bonus (1.09) should be added into G8 and H8 (and all other iterations of the same template)? Or is LO calculated somewhere else that I've not discovered yet?

K7 adds the overload proc chance * J8, which is =I8*(1+G8*H8). In other words, I calculate the average overload damage, and then add a % of that to the average LB damage to get an overall value.

Originally Posted by Caelwynar

Your observation looks correct to me, though I'm not quite comfortable changing the theorycrafting formulae without a reference or test data to verify that it's valid.

The non-crit damage for LB's Overload looks too high as well (more than half of LB's non-crit in the rotation tables), because Bink didn't divide the Shamanism bonus by 2 like he did for every other portion of the Overload coefficient.

I don't know if that was intentional and LB overloads really do more than half of a LB in-game, or if it's a mistake in the spreadsheet.

Shamanism is applied after the overload halving. We established that fact in ldrx's ESSE thread a while back.

Zamir · 10/22/09, 6:28 PM

Originally Posted by Binkenstein

K7 adds the overload proc chance * J8, which is =I8*(1+G8*H8). In other words, I calculate the average overload damage, and then add a % of that to the average LB damage to get an overall value.

Cells G8 and H8 are empty on my version of the spreadsheet (r20.1), implying that the average value arrived at in J8 and being multiplied in K7 is just for LBO hits - that is, not including crits. Row 8, which deals with LBO, seems to have identical formulae to row 7 (except pointing to row 8 cells instead of row 7). I noticed that I7 - Lightning Bolt Hit - has a lower value to J7 - Lightning Bolt average damage - which is as expected due to J7 factoring in crits (=I7*(1+G7*H7)). But I8 - Lightning Bolt Overload hit - has the same value as J8 - Lightning Bolt Overload average damage.

Actually, a screenshot would probably help:

I apologise if this is unclear, I've never had to discuss a spreadsheet before!

Thanks for taking the time to help us understand, it's much appreciated.

Caelwynar · 10/22/09, 7:48 PM

Originally Posted by Binkenstein

K7 adds the overload proc chance * J8, which is =I8*(1+G8*H8). In other words, I calculate the average overload damage, and then add a % of that to the average LB damage to get an overall value.

Yep. The problem is that G8 and H8 are blank, so LB overload's average damage is just equal to the normal hit damage.

Originally Posted by Binkenstein

Shamanism is applied after the overload halving. We established that fact in ldrx's ESSE thread a while back.

Cool, thanks for the input.

I'm playing catch-up on the theorycrafting, and just realized that the default forum settings on EJ were hiding old threads like that ESSE one you mentioned. I'll read through the older stuff and try to get up to speed.

Binkenstein · 10/22/09, 9:22 PM

Hmm, yeah, I think that was one of the things that I'd missed. I think it was originally looking at the LB values (which are the same anyway).

Caelwynar · 10/22/09, 10:58 PM

Here's today's previous update with the LB Overload fix:

SEIC 3.2.2_Pahvel_102209b.xls

Zamir · 10/22/09, 11:01 PM

Clicking that link only shows me the previous version (102009).

Caelwynar · 10/23/09, 12:06 AM

Originally Posted by Zamir

Clicking that link only shows me the previous version (102009).

Sorry bout that. When I set up that particular share link, I had mistakenly only shared the original .xls file and not the whole folder. Fixed now.

Caelwynar · 10/24/09, 2:51 PM

I'm starting to add the calculations for the T10 set bonuses to the spreadsheet, but am running into some circular reference headaches. (Average crit is based on EM's cooldown. EM's cooldown is based on number of LBs cast. Number of LBs cast in the rotation model involves some math looking up LB's average value, which is based on average crit.)

To avoid the circular reference, I will probably have to resort to estimating the number of LBs cast based on haste rather than actually counting them from the rotation tables. This will resort in slightly lower accuracy in the model with regards to the T10 2-piece bonus, and the Chain Lightning usage models may throw it off even more, but the differences should be minimal. (If the estimate is off by 1 lightning bolt per rotation from the actual model, you gain or lose between 0.04% and 0.08% average crit from the estimate's error). As there are other such small errors inherent to the model anyway, this is probably acceptable.

Zamir · 10/24/09, 5:24 PM

What if you remove Elemental Mastery from the current rotation calculations altogether? This should result in a more accurate evaluation of LB's average damage for the purposes of working out a "normal" rotation. Then use another new iteration of the rotation calculation that factors in elemental mastery, much like there are separate calculations to factor in CL use, heroism etc.

You could then use the normal rotation's total LB count to calculate how much time is taken off EM's cooldown, and use that number to add EM as a flat DPS value to the final output:

time * 60 / (180-cooldown reduction) = how many EMs used (this could be a real number for formulation purposes or an integer for actual benefit over given fight time)
EM DPS minus Normal DPS * (15 * how many EMs used) = total extra damage from EM
Extra EM damage / ( time * 60 ) = extra EM DPS

Of course this still wouldn't represent a real fight scenario as most of us save EM for use with other cooldowns (BL), but you could model that too if you wanted, since the spreadsheet already has the mechanisms in place.

(Normal disclaimers about my lack of math skill/experience apply, I apologise if this is stupid and/or wrong)

Caelwynar · 10/24/09, 8:45 PM

I got the 2-piece bonus implemented using Bink's original method (averaging the crit gained similar to trinket procs), taking into account the cooldown reduction. As expected, the dps value is pathetic. You gain 0.63% average crit with my character's haste values, for a whopping ~31 dps.

I implemented the 4-piece bonus fairly easily, and the results suck (~76 dps increase), but I'm not sure I fully trust that number because of how the spreadsheet model works. It figures out the spell cast "rotation" and dps for the duration of a single flame shock. Regardless of whether the cooldown on LvB is 6.5 seconds or 8, you wind up with 2 LvB casts during that flame shock, which results in practically no impact to the numbers. You cast the same spells in a slightly different order. Based on that 1.5 second cooldown reduction, though, you should be seeing around a 23% (8/6.5 = ~1.23) increase in the number of LvBs cast over the duration of a fight, which isn't reflected in the model as it currently exists.

I'm considering moving all of the calculations from the New Rotation sheet into a VBA macro which will simulate the entire fight duration instead. Aside from potentially being more accurate, I'm more comfortable dealing with VB code than I am trying to keep track of all the cells and formulas in the spreadsheet, and would be much more confident about releasing the results.

Zamir · 10/24/09, 9:42 PM

If you set latency to 0, select all haste buffs except Electrifying Wind and set your haste value to 848 you'll see a sudden massive jump in the value of 4t10 due to the rotation calc adding another LvB in at the end. Two factors are inflating it: first, because the LvB cast starts at 18:00, which is when Flame Shock falls off and this is not accounted for; second, because the rotation then assumes FS->LvB->(LBs)->LvB->(LBs)->LvB---> repeat. That is to say, 3 lava bursts per flame shock for every flame shock.

Even if you resort to simulation, how would you actually construct the 4t10 'rotation'?