Elitist Jerks Hunter Spreadsheets -- In development

 05/04/07, 1:23 PM #151 Norwest Von Kaiser   Vinehead Troll Mage   Thunderhorn Modelling Aspect of the Hawk For each individual shot, iAotH is either on or off, so I tried thinking about the problem shot-by-shot instead of per-second. What I did was calculate the average number of shots a proc lasts, counting reprocs and everything. I also calculated the number a shots a 'no-proc' streak lasts. If we know the average number of shots in a proc chain and the average number of shots between procs we can easily find the average number of hastened shots. No-proc Streak: Pretty simple, Hawk has a 1 / 10 chance to proc, the average length of a 'no-proc' streak is 10 slow shots. The fact that Quick Shots doesn't add haste to the shot it procs off of is accounted for. Proc-Streak The length of a chain of procs is a bit harder to model. But it can be done by calculating the number of fast shots inside a proc, and calculating the chance that one of those shots will chain the proc a second/third/fourth time. Each extra chained proc adds (on average) half of the duration of the proc. I modelled up to 15 procs in a row. One small twist, is that the first shot is not hastened when iAotH procs. That makes 1st time procs and chained procs behave slightly differently depending on the bow speed. After modelling it, I recalculated the probabilities using a Taylor Series and got the same answer. If you don't know what that is, don't worry, because, I don't really know either I just know it works. Here's my calculation Spreadsheet: http://www.mediafire.com/?8dnxqmdqddw If people like my approach to this, I'll work it into the original spreadsheet. I could get the whole thing down to 2-3 lines easily, but I don't want to do that before I think people agree with my approach. I'm always very bad at explaining my mathematical thinking to people, I apologize I'm sure this is confusing. I'll be on tomorrow to answer any questions etc.
05/04/07, 1:27 PM   #152
Lactose
Don Lactose

Tauren Hunter

Talnivarr (EU)
 Originally Posted by Dibbler Lactose - I use your spreadsheet primarily as a way to compare possible gear. I.e. do I want AP gems or crit, gloves #1 or gloves #2 etc. So what I'll do is enter in all my stats according to my armory info. I put the same damage values in for weapon excluding any bonuses (no +AP, stamina, crit. .. etc). In the same weapons section where you can add bonuses, I will input the stats from the two items I am comparing to see which one offers the most benefits based on my toons set up. That being said, I would really like to have a section where we could enter a piece of armor (very similar to comparing ranged weapons).
I'm not sure what you mean, actually.
Do you mean:
A -- With setup 1, will my new pair of gloves (gloveX) be an upgrade to what I have in setup 1?
B -- With setup 1, what will be the best upgrade, gloveX or gloveY?

A should be possible already, current / new item(s), B isn't done as of now, and would require some rearranging.

Last edited by Lactose : 05/04/07 at 1:29 PM. Reason: Added quote.

Look, Lactose, we'd rather you didn't eradicate the whole human race.
- Sam & Max

 05/04/07, 1:33 PM #153 Nightarcher Von Kaiser   Nightarcher Murloc Hunter   Tarren Mill (EU) Ok let me get it straight. Lactose your table is while buggy in some places if you ignore that its more accurate for calculating what is better 1agi or 2 AP? Cheecky your table is good for the big picture of dps but for fine tuning is not really good since using 50 shot sim instead of actual formulas? In summary I should use the ratio values of Lactose atm to compare items with very minor difference? Like which gem to use 8 agi or 16 ap?
05/04/07, 1:48 PM   #154
Dibbler
Von Kaiser

Tauren Hunter

Dalvengyr
 Originally Posted by Lactose I'm not sure what you mean, actually. Do you mean: A -- With setup 1, will my new pair of gloves (gloveX) be an upgrade to what I have in setup 1? B -- With setup 1, what will be the best upgrade, gloveX or gloveY? A should be possible already, current / new item(s), B isn't done as of now, and would require some rearranging.

I use it for example A mostly but I can wiggle the information to fit example B.

05/04/07, 1:49 PM   #155
Cheeky
Great Tiger

Cheeky
Troll Hunter

No WoW Account
 Originally Posted by Nightarcher Ok let me get it straight. Lactose your table is while buggy in some places if you ignore that its more accurate for calculating what is better 1agi or 2 AP? Cheecky your table is good for the big picture of dps but for fine tuning is not really good since using 50 shot sim instead of actual formulas? In summary I should use the ratio values of Lactose atm to compare items with very minor difference? Like which gem to use 8 agi or 16 ap?
I guess it all dpends with what you are looking for. I'm going to remove some of the explicit rounding in mine and just scale what the cell shows in terms of precision displayed. Excel keeps all the true values through the calculations to the end. That should eliminate the discrepencies between the output of Lactose's and mine.

I never really bothered to provide much of a 1 more X or X is equal to so much Y function to mine. I figured you could just make the change at the item/enchant/gem/talent level and see what the result is. If you are comparing 3-4 different item sets of gear I think mine is a bit easier to use, but for heads-up compares Lactose provides both sets of results simultaneously.

While mine requires me to keep on top of gear stats, it does have the advantage of not requiring you to have Armory, Wowhead or your character available to use. Just pick the gear by name and you're done.

I have also used my shot rotation to figure out exactly what the best way to use trinkets, racials, rapid fire, etc is. It's nice to be able to say in 12 seconds I can deal X amount of damage every 3 minutes. Without having to hand count shots and total damage up on my own.

 05/04/07, 2:14 PM #156 Aesa Glass Joe   Aesa Night Elf Hunter   Haomarush Compensating for the 50 shot calculation Cheeky, I think the inaccuracy in the 50 shot method of dps calculation arises from the fact that right now it assumes that the 50 shot sequence will restart at the time that shot #50 finishes, which almost always is not true. If you let the user select a 51st shot that would theoretically restart the whole rotation, you could use the starting time of that shot for your Total Time value. The 51st shot would not have its damage added to the Total Damage value, however. With that minor tweak, it shouldn't matter if you only select one Auto Shot and one Steady Shot in your rotation, provided rounding errors aren't an issue.
05/04/07, 2:22 PM   #157
Kolusius
King Hippo

Night Elf Hunter

Argent Dawn
 Originally Posted by Dibbler On a further tangent, has anyone looked a graphing the AP vs Crit vs damage to see if a local max can be found?
I made a 3D graph at one point of crit vs rap at one point, with damage as the z axis, its hard to really infer anything through it though. There is one for auto shot, multi shot, steady shot, and one for arcane shot, and I used weighting with the results of each to get a combined (rough) "shot rotation" type graph, (4 auto, 3 steady, 2 arcane, 1 multishot) based off the rotation I generally repeat. I suppose I can divide by a time element to get it for dps as well.

It showed a definate "sweet spot" in the ap/crit ratio, but by the time I was finished, I wasnt really sure what I was looking for. I can see where my current stats are on the graph, and decide oh, if I add 40 attack power, and take away 4 crit rating, I'll be right on the sweet spot. But how do you accomplish that? I guess you could to a small degree with gems, but what does that accomplish?

Adding crit always increases damage. Adding rap always increases damage. Moving towards the sweet spot is fine, but moving away from it will increase your damage as well (as long as its a positive change to ap or crit, depending on which side you fall).

If I could figure out an exact equation for the crest of the curve, it would be useful, at least for getting an ap/crit ratio, and all the math/calculus I did in college was theoretical, not practical. Too many Number Theory classes, not enough applied math classes. Give me real numbers and I'm at a loss.

Anyways, I can check if I still have them laying around, and post them if you're interested. They're pretty rudimentary, and I've yet to really find a use, other than looking at pretty colors. I'll dig around and see if I still have them.

05/04/07, 2:28 PM   #158
Cheeky
Great Tiger

Cheeky
Troll Hunter

No WoW Account
 Originally Posted by Aesa Cheeky, I think the inaccuracy in the 50 shot method of dps calculation arises from the fact that right now it assumes that the 50 shot sequence will restart at the time that shot #50 finishes, which almost always is not true. If you let the user select a 51st shot that would theoretically restart the whole rotation, you could use the starting time of that shot for your Total Time value. The 51st shot would not have its damage added to the Total Damage value, however. With that minor tweak, it shouldn't matter if you only select one Auto Shot and one Steady Shot in your rotation, provided rounding errors aren't an issue.
That's a damned good idea. I'm going to add that now and see if I can get it to match the theoretical DPS. You'll still run into issues with Multi-Shot and Arcane Shot Cooldowns if you haven't taken them into account for your "finishing" shot, but that becomes user error then.

Doing that and using a Auto/Steady rotation gets my DPS calculated to 922.14 vs. Lactose's 921.88. I think 0.03% difference is pretty negligable. I'm going to look into tweaking the code that builds the priority rotation to try and find Steady Shot patterns between Multi and Arcane, maybe I can have it stop early when it has completed a cycle.

05/04/07, 4:11 PM   #159
Cheeky
Great Tiger

Cheeky
Troll Hunter

No WoW Account
 Originally Posted by Norwest If people like my approach to this, I'll work it into the original spreadsheet. I could get the whole thing down to 2-3 lines easily, but I don't want to do that before I think people agree with my approach. I'm always very bad at explaining my mathematical thinking to people, I apologize I'm sure this is confusing. I'll be on tomorrow to answer any questions etc.
Norwest, I think you may be complicating it a bit more than you need to. [later - but after working through the numbers, maybe not.]

Rather than solve for the uptime, lets solve for the chance on any on shot that you are not in Quick Shots.

To do this you need to figure out how many shots have happened that you know it hasn't proc'd. This would be floor(12/quick speed), quick speed for a 2.8s weapon with quiver and SS is 1.76, so 6 shots.

Then we know that the chance all of those 6 shots did not proc Quick Shots is (1-chance for QS)^(shots), or .9^6 = 53.14%. That means there is 46.86% chance that one of those 6 proc'd Quick Shots. This chance is true for any shot S in an infinite series of shots. So we can generalize the up time to 46.86%

Using a 2.7s weapon like in your spreadsheet yields an uptime of 1 - (.9)^floor(12/(2.7/(1.15 * 1.3 * 1.2))) = 52.17% uptime.

The only difference is that you are trying to account for the fact that for some weapons there is a difference in the number of shots that can fit into the 12 second window when it is Quick and when it is normal. I think we can account for this by averaging the two values, which while not exact is pretty easy to do. Doing this for a 2.7s weapon yields 49.58%, about 2% off from your calculations. This methodology fails in assuming half the time QS won't be up before our shot and 1/2 the time it will be, when in truth it's the same ratio as QS itself.

In the interests of accuracy we should try using your calculations, but they make an assumption too, they assume there is 0 auto shot clipping in the shot rotation. We can account for this by using the Shot Rotations matrix to compute autoshots/second, which works even if there is clipping.

This is very interesting, and good work on your part. I'm going to think about this a bit more, but so far I think your procedure is superior to what I am using now. (Maybe it's worth looking at Frenzy the same way.)

05/04/07, 4:24 PM   #160
The Iron Colonel
Don Flamenco

Dwarf Hunter

Mug'thol
 Originally Posted by Norwest For each individual shot, iAotH is either on or off, so I tried thinking about the problem shot-by-shot instead of per-second. What I did was calculate the average number of shots a proc lasts, counting reprocs and everything. I also calculated the number a shots a 'no-proc' streak lasts. If we know the average number of shots in a proc chain and the average number of shots between procs we can easily find the average number of hastened shots. No-proc Streak: Pretty simple, Hawk has a 1 / 10 chance to proc, the average length of a 'no-proc' streak is 10 slow shots. The fact that Quick Shots doesn't add haste to the shot it procs off of is accounted for. Proc-Streak The length of a chain of procs is a bit harder to model. But it can be done by calculating the number of fast shots inside a proc, and calculating the chance that one of those shots will chain the proc a second/third/fourth time. Each extra chained proc adds (on average) half of the duration of the proc. I modelled up to 15 procs in a row. One small twist, is that the first shot is not hastened when iAotH procs. That makes 1st time procs and chained procs behave slightly differently depending on the bow speed. After modelling it, I recalculated the probabilities using a Taylor Series and got the same answer. If you don't know what that is, don't worry, because, I don't really know either I just know it works. Here's my calculation Spreadsheet: http://www.mediafire.com/?8dnxqmdqddw If people like my approach to this, I'll work it into the original spreadsheet. I could get the whole thing down to 2-3 lines easily, but I don't want to do that before I think people agree with my approach. I'm always very bad at explaining my mathematical thinking to people, I apologize I'm sure this is confusing. I'll be on tomorrow to answer any questions etc.
For the sake of discussion, I'll mention that you can use a very similar method (a power series, that is) to describe the behavior of the new Darkmoon card (wrath). More often than not a series probably describes the proc functions more accurately than simple probability, but they also lead to more headaches due to their relative complexity.

05/04/07, 6:51 PM   #161
Jezele
Piston Honda

Draenei Shaman

 Originally Posted by Lactose On a tangent to this, people who've used both, which do you prefer, and why? Or what do you feel as missing in the spreadsheets?
I've played around with both, but ultimately decided to create my own based on your formulas.

I've primarily used Cheeky's - not only because it was easier to "steal" formulas from, but I liked the way that everything was laid out so that you could see the "guts" of how various changes in gear would affect different variables of your play. I've been going with simple rotations, so the ability to model a more complex rotation is a non-factor for myself, but that's also a great tool (though I think I would have gone with a time cut-off rather than an arbitrary number of shots). I also liked how easy it was to compare various talent setups.

Overall, I think both are great tools, but what I found lacking in both was an easy way to compare multiple pieces of gear to determine how much of an upgrade various options are. Lactose's allows a small comparison sample, whereas it's very cumbersome with Cheeky's as you have to write down the DPS values for each individual piece of gear if you want to compare them. This is actually why I created my own, which lists a fair number of potential upgrades, then calculates the resulting change in DPS for each one so that I can prioritize which pieces of gear to go after. I didn't bother putting in a lot of options relating to talent setup or rotations, so it's a lot simpler for me to do, but it filled the need of being able to compare multiple options at a glance.

So basically, I think both are great tools, but since they didn't quite fit my own personal needs, I ended up crafting my own. But I couldn't have done it without the guidance of both of your great spreadsheets.

 05/05/07, 10:09 AM #162 Trieste Von Kaiser   Trieste Orc Hunter   Archimonde (EU) I have a problem to open your spreadsheet cheeky! It sais that there's a password but in a language which is not taken into account... need help Last edited by Trieste : 05/05/07 at 10:33 AM.
05/05/07, 10:58 AM   #163
The Iron Colonel
Don Flamenco

Dwarf Hunter

Mug'thol
 Originally Posted by Jezele I've played around with both, but ultimately decided to create my own based on your formulas. I've primarily used Cheeky's - not only because it was easier to "steal" formulas from, but I liked the way that everything was laid out so that you could see the "guts" of how various changes in gear would affect different variables of your play. I've been going with simple rotations, so the ability to model a more complex rotation is a non-factor for myself, but that's also a great tool (though I think I would have gone with a time cut-off rather than an arbitrary number of shots). I also liked how easy it was to compare various talent setups. Overall, I think both are great tools, but what I found lacking in both was an easy way to compare multiple pieces of gear to determine how much of an upgrade various options are. Lactose's allows a small comparison sample, whereas it's very cumbersome with Cheeky's as you have to write down the DPS values for each individual piece of gear if you want to compare them. This is actually why I created my own, which lists a fair number of potential upgrades, then calculates the resulting change in DPS for each one so that I can prioritize which pieces of gear to go after. I didn't bother putting in a lot of options relating to talent setup or rotations, so it's a lot simpler for me to do, but it filled the need of being able to compare multiple options at a glance. So basically, I think both are great tools, but since they didn't quite fit my own personal needs, I ended up crafting my own. But I couldn't have done it without the guidance of both of your great spreadsheets.
Easiest way That I've found to compare gear sets in Cheeky's sheet is to simply make several copies of the sheet. That way you can directly compare gear sets side by side. Not an elegant solution, but certainly workable.

05/05/07, 11:10 AM   #164
Cheeky
Great Tiger

Cheeky
Troll Hunter

No WoW Account
 Originally Posted by Trieste I have a problem to open your spreadsheet cheeky! It sais that there's a password but in a language which is not taken into account... need help
That's odd. It only works with Microsoft Excel, not any other viewer. The whole thing uses Arial as the font, which I think is pretty common.

What application are you using to open it?

 05/05/07, 11:16 AM #165 Trieste Von Kaiser   Trieste Orc Hunter   Archimonde (EU) I use microsoft excel lol First time I have this problem. It's really weird. I just get openoffice and it works... I don't really understand lol...

 Elitist Jerks Hunter Spreadsheets -- In development