Hunter Spreadsheets -- In development - Page 49

Cheeky · 09/13/07, 1:56 PM

Originally Posted by Jakt

Alright, went to Dr Boom and fired a few hundred shots while keeping Hunter's Mark up. This has gotten me a little closer to actually observed numbers, but I am still finding I need to put in 37.5% mitigation to get my observed numbers. Still doesn't seem right. Maybe there are input ranges for which the spreadsheet is less accurate?

Originally Posted by Lactose

Tested, Dr. Boom has 37.0% armor mitigation, +/- 0.3%.

Let's hear it for the math!

Looks like the problems you were seeing in raids are through HM expiring. We tend to give one Hunter the responsibility to keep it up. Same guy who does Scorpid Sting, so he's used to watching cool downs.

Jakt · 09/13/07, 2:24 PM

Originally Posted by Cheeky

Let's hear it for the math!

Looks like the problems you were seeing in raids are through HM expiring. We tend to give one Hunter the responsibility to keep it up. Same guy who does Scorpid Sting, so he's used to watching cool downs.

Yea, happy that it all worked out. Now I just need to learn how to play ;-)

Kaber · 09/13/07, 4:15 PM

Originally Posted by khel

Yep. Using Cheeky's spreadsheet and my current gear, with my typical raid buffs, I came up with the following values:

1 agi = 0.639 dps
1 ap = 0.276 dps
1 cr = 0.530 dps

I just multiplied by 10 when setting the values to make things a bit easier, (1agi = 6.39, 1ap = 2.76, 1cr = 5.30), and estimated some values for hit rating (because it caps), intellect, mana/5, and stamina. Really these values fluctuate from fight to fight, depending on your assignment and group setup...since sometimes you might have a shadowpriest and then as BM, intellect and mana/5 become useless. Some fights stamina is worth more than others. On Vashj you don't need to max out your hit rating really to kill elementals and bats effectively.

I just used:

1 agi = 6.39
1 ap = 2.76
1 cr = 5.3
1 hr = 5.0
1 int = 1.0
1 stam = 1.0
1 mana/5 = 6.39

and feel fairly confident with the results so far.

I would recommend using Hit's actual value in the spread sheet until you reach cap and then 0 it out to get more accurate results. You may be devalueing Hit items that would be an upgrade.

Cheeky · 09/14/07, 1:37 AM

Well, I spent a ton of time (too much actually) on this tonight. I've gotten a lot of suggestions from everyone for fixes/feature/comments. Here is a listing of the change log right now:

- Fix Draenei racial to actually be counted as +1% to hit.
- Added new gear (Halaani Razorshaft, Halaani Grimshot).
- Fixed the fact that Madness of the Betrayer was always in effect, even if you didn't have the trinket.
- Updated model of Dragonspine Trophy to include a user-provisionable cooldown. Default is 20 seconds. The model had to completely change to support the internal cooldown, setting it to 0 will not generate the same numbers given previously. Please use verison 28 of the spreadhseet to get numbers for 2.1 with the Dragonspine Trophy
- Provided the user the ability to select 2.1 or 2.2 Haste Rating conversion rates. (This will be removed when 2.2 goes live.)
- Corrected some equations that would cause "Divide by 0" errors in certain shot rotations.
- Allow for multiple, user-named gear sets. 5 different sets can be maintained. The last DPS value of that set is stored as well. (This feature not available to Open Office users.)
- Added AutoOnly buttons to the Shot Rotations. This should make it eaiser to hand-build custom rotations. (This feature not available to Open Office users.)
- On a brilliant suggestion from Lactose, there is now a button (CalcAttributes) on the gear page which will instantaneously give you the DPS advantage of adding one more Agility, AP, crit, or hit. You can also enable this feature to recalculate the values every time you change gear or hand adjustments. Set Loads are exempted from this recalculation because it made loading agonizingly slow. (This feature not available to Open Office users.)

Please post any additional suggestions (or pm me) that you may have that I've missed. There's a lot of cool stuff in here which should make it a little easier to evaluate future gear and compare whole sets of items. If I don't get anything major, I'll have this out tomorrow evening or early Saturday (depending on how much drinking I do tomorrow.)

Lactose · 09/14/07, 8:45 AM

Just adding to my previous suggestion a bit...

With the table stating how much DPS you gain with +1 stat, this could be used to create a matrix like the following:

Kolusius · 09/14/07, 10:00 AM

Armor penetration on gear affecting pets also, if you didn't get around to already fixing it!

(e) To clarify when I'm actually seeing it. If I set armor to a nonzero amount, and switch to the pet calculations tab, you can see it in effect at the correct mitigation based on the results tab. If you equip the choker of serrated blades, you'll see the armor value drop on both the results tab, and the pet calculations tab.

Cheeky · 09/14/07, 10:27 AM

Originally Posted by Kolusius

Armor penetration on gear affecting pets also, if you didn't get around to already fixing it!

(e) To clarify when I'm actually seeing it. If I set armor to a nonzero amount, and switch to the pet calculations tab, you can see it in effect at the correct mitigation based on the results tab. If you equip the choker of serrated blades, you'll see the armor value drop on both the results tab, and the pet calculations tab.

Ah, I never thought of chcking that. I guess I'll have to compute pet armor mitigation separately. Easy enough to do. Thanks.

Kolusius · 09/14/07, 10:48 AM

Yep, no problem. I tested it on a blasted lands mob for a couple thousand shots just to make sure, my pet ended up with the exact same average damage, high damage, and one lower for the bottom end. Which makes sense, because the choker drops my attack power a little.

Cheeky · 09/14/07, 4:20 PM

Both Excel and OO versions are available (there are no histories kept for OO)

Change Log:
- Fix Draenei racial to actually be counted as +1% to hit.
- Added new gear (Halaani Razorshaft, Halaani Grimshot).
- Fixed the fact that Madness of the Betrayer was always in effect, even if you didn't have the trinket.
- Updated model of Dragonspine Trophy to include a user-provisionable cooldown. Default is 20 seconds. The model had to completely change to support the internal cooldown, setting it to 0 will not generate the same numbers given previously. Please use version 28 of the spreadsheet to get numbers for 2.1 with the Dragonspine Trophy
- Provided the user the ability to select 2.1 or 2.2 Haste Rating conversion rates. (This will be removed when 2.2 goes live.)
- Corrected some equations that would cause "Divide by 0" errors in certain shot rotations.
- Allow for multiple, user-named gear sets. 5 different sets can be maintained. The last DPS value of that set is stored as well. (This feature not available to Open Office users.)
- Added AutoOnly buttons to the Shot Rotations. This should make it easier to hand-build custom rotations. (This feature not available to Open Office users.)
- On a brilliant suggestion from Lactose, there is now a button (CalcAttributes) on the gear page which will instantaneously give you the DPS advantage of adding one more Agility, AP, crit, or hit. You can also enable this feature to recalculate the values every time you change gear or hand adjustments. Set Loads are exempted from this recalculation because it made loading agonizingly slow. (This feature not available to Open Office users.)
- Added an Attribute Equivalency Matrix to the 'Settings and Results' tab to easily compare item stat values.
- Pets no longer benefit from Hunter's armor penetration stat.
- Corrected some gear stats (Belt of the Black Eagle).
- Adjusted proc rate of Don Santos Famous Hunting Rifle to be 5%. My initial testing has been bettered by more thorough work.

Zedd · 09/14/07, 5:23 PM

Thank you cheeky

Lactose:
I know you are looking into armor of bosses/mobs, I ripped this from the shaman dps topic:

["Hydross the Unstable:-1"] = {
["MeleePercent"] = 90.65588344578481,
["TargetDamage"] = 12086851,
["RangedHits"] = 0,
["TargetPoints"] = 361,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 135,
["The Lurker Below:-1"] = {
["MeleePercent"] = 72.61636977902651,
["TargetDamage"] = 14461548,
["RangedHits"] = 0,
["TargetPoints"] = 384,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 106,
["Leotheras the Blind:-1"] = {
["MeleePercent"] = 90.4753846722851,
["TargetDamage"] = 12706970,
["RangedHits"] = 0,
["MeleeHits"] = 131,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 361,
["Fathom-Lord Karathress:-1"] = {
["MeleePercent"] = 48.85333228480048,
["TargetDamage"] = 7065359,
["RangedHits"] = 0,
["MeleeHits"] = 67,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 398,
["Morogrim Tidewalker:-1"] = {
["MeleePercent"] = 248.9926252935603,
["TargetDamage"] = 36014657,
["RangedHits"] = 0,
["TargetPoints"] = 636,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 358,
["Lady Vashj:-1"] = {
["MeleePercent"] = 182.5408140306064,
["TargetDamage"] = 20643325,
["RangedHits"] = 0,
["TargetPoints"] = 435,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 246,
["Al'ar:-1"] = {
["MeleePercent"] = 85.85027964112118,
["TargetDamage"] = 10978248,
["RangedHits"] = 0,
["TargetPoints"] = 486,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 127,
["Void Reaver:-1"] = {
["MeleePercent"] = 65.54655061008738,
["TargetDamage"] = 17031095,
["RangedHits"] = 0,
["TargetPoints"] = 377,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 101,
["High Astromancer Solarian:-1"] = {
["MeleePercent"] = 74.72848204403455,
["TargetDamage"] = 10930465,
["RangedHits"] = 0,
["TargetPoints"] = 365,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 103,
["Kael'thas Sunstrider:-1"] = {
["MeleePercent"] = 176.3647025877637,
["TargetDamage"] = 16137281,
["RangedHits"] = 0,
["TargetPoints"] = 452,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 218,
["Rage Winterchill:-1"] = {
["MeleePercent"] = 74.98291001343254,
["TargetDamage"] = 10207405,
["RangedHits"] = 0,
["MeleeHits"] = 100,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 240,
["Anetheron:-1"] = {
["MeleePercent"] = 110.0134920667448,
["TargetDamage"] = 10566031,
["RangedHits"] = 0,
["MeleeHits"] = 151,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 290,
["Kaz'rogal:-1"] = {
["MeleePercent"] = 34.02481719993865,
["TargetDamage"] = 4079367,
["RangedHits"] = 0,
["MeleeHits"] = 45,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 96,
["Azgalor:-1"] = {
["MeleePercent"] = 28.99024631795682,
["TargetDamage"] = 3797048,
["RangedHits"] = 0,
["MeleeHits"] = 39,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 91,
["Archimonde:-1"] = {
["MeleePercent"] = 136.3754397064038,
["TargetDamage"] = 11542858,
["RangedHits"] = 0,
["MeleeHits"] = 187,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 256,
["High Warlord Naj'entus:-1"] = {
["MeleePercent"] = 78.98313304195391,
["TargetDamage"] = 13013574,
["RangedHits"] = 0,
["MeleeHits"] = 114,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 353,
["Supremus:-1"] = {
["MeleePercent"] = 25.74575904314052,
["TargetDamage"] = 7992773,
["RangedHits"] = 0,
["MeleeHits"] = 37,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 182,
["Shade of Akama:-1"] = {
["MeleePercent"] = 19.31851167371959,
["TargetDamage"] = 2898413,
["RangedHits"] = 0,
["TargetPoints"] = 295,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 26,
["Teron Gorefiend:-1"] = {
["MeleePercent"] = 219.0667478831767,
["TargetDamage"] = 25422540,
["RangedHits"] = 0,
["TargetPoints"] = 508,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["MeleeHits"] = 300,
["Gurtogg Bloodboil:-1"] = {
["MeleePercent"] = 164.3253322910583,
["TargetDamage"] = 21503518,
["RangedHits"] = 0,
["MeleeHits"] = 236,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 386,
["Essence of Suffering:-1"] = {
["MeleePercent"] = 115.9768434646575,
["TargetDamage"] = 15004562,
["RangedHits"] = 0,
["MeleeHits"] = 137,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 663,
["Essence of Anger:-1"] = {
["MeleePercent"] = 31.58787810114778,
["TargetDamage"] = 14179397,
["RangedHits"] = 0,
["MeleeHits"] = 71,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 472,
["Essence of Desire:-1"] = {
["MeleePercent"] = 186.3774201634872,
["TargetDamage"] = 17665047,
["RangedHits"] = 0,
["MeleeHits"] = 215,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 582,
["Mother Shahraz:-1"] = {
["MeleePercent"] = 926.288844926868,
["TargetDamage"] = 48750263,
["RangedHits"] = 0,
["MeleeHits"] = 1221,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 1330,
["Gathios the Shatterer:-1"] = {
["MeleePercent"] = 101.5948107769565,
["TargetDamage"] = 11759165,
["RangedHits"] = 0,
["MeleeHits"] = 147,
["LastLevel"] = 70,
["RangedPercent"] = 0,
["TargetPoints"] = 970,

The lua from the mod says:

--FIRST STEP: CALCULATE ARMOR
local pr = 0;
if ( TotalMeleeHits > TotalRangedHits ) then
pr = 1 - ( TotalMeleePercentage ) / ( TotalMeleeHits );
else
pr = 1 - ( TotalRangedPercentage ) / ( TotalRangedHits );
end

local ea = TargetArmor_PercentToArmor(pr, PlayerLevel);

pr = floor(pr*10000+.5)/100;
ea = floor(ea+.5);

Credit to Rava

• vank · 09/14/07, 8:57 PM

Thank you so much, Cheeky... again.

QuiggyB · 09/15/07, 4:37 AM

The numbers the sheet is calculating for the +1 attributes seem quite a bit different than what I got with the previous sheet making a custom offhand, putting 1 in each ability slot and comparing the change in personal and total dps. Here is an example for a SV build and a BM build with the same gear. For each build the first line is personal dps, the second line is total dps. I did put -50 hit on the custom offhand to get myslef under the hit cap but I checked it with 0 and it did not substantially change the values (other than hit). To be clear, thats the values version 28 gave vs the values 29 is auto-computing. I didnt have time to do the same test on version 29. FWIW I did have a dragonspine equipped so its possible the haste change is throwing the numbers.

Cheeky · 09/15/07, 11:02 AM

Originally Posted by QuiggyB

The numbers the sheet is calculating for the +1 attributes seem quite a bit different than what I got with the previous sheet making a custom offhand, putting 1 in each ability slot and comparing the change in personal and total dps. Here is an example for a SV build and a BM build with the same gear. For each build the first line is personal dps, the second line is total dps. I did put -50 hit on the custom offhand to get myslef under the hit cap but I checked it with 0 and it did not substantially change the values (other than hit). To be clear, thats the values version 28 gave vs the values 29 is auto-computing. I didnt have time to do the same test on version 29. FWIW I did have a dragonspine equipped so its possible the haste change is throwing the numbers.

The [Dragonspine Trophy] is going to make difference, I'm just not sure how much of one. When I do this same test for my gear I get numbers that are exact for AP, and 0.01 off (probably rounding/display error) for Crit and Agility. Also, try re-generating the "+1" numbers after putting the -50 hit, but before adding in the other stats, to give yourself an accurate baseline.

QuiggyB · 09/15/07, 2:02 PM

Found something else. It seems when blessing of kings is selected AND rank 2 (67agi) or rank 3 (77agi) GoA is selected the values computed for agility are roughly 2x what they should be. If you set GoA to no and put 67 or 77agi under a custom offhand you get the same result. If you modify the number by 1 up or down the value for agi drops in half again.

e.g. BOK & 77agi custom offhand = 1.06dps per agility. BOK % 78agi = 0.53dps per agility.

I got these results with a 41/20BM build and a 21/40 SV build. I did not get this result with a 5/47/9 MM build. For the BM and SV builds I had 5/5 lethal, 5/5 Eff, 2/2 GFTT, 2/2 rapid, aimed, and 5/5 Mortal. for the MM build I had 5/5 IAotH, the marks tree filled up including 5/5 Imp Arcane, and 9 points in the bottom tier of the surv tree.

I just tried it looking more at the MM talents. I get this 2x result until I put points in combat experience at which point it goes away. I also reset all talents to zero, got the 2X issue, put two points in combat experience and it went away. for whatever reason if I start with a SV or BM build though putting points in CE does not make the issue go away. In all cases having an agi value other than 67 or 77 in combination with BOK does make it go away.

Cranch · 09/15/07, 2:34 PM

Nice job on the CalcAttributes button! I just use the hand-adjust section, so never thought to make the suggestion.

I would suggest you add int, haste rating, and armor penetration as well, as they also affect dps (that's what I use in my item rating spreadsheet.)

I heard a rumor that the Blizzard developers are using your spreadsheet to analyze hunter dps. Maybe you can check if the computer running the spreadsheet is in the blizzard domain and silently degrade dps by 15%...