◊ Lactose

Cheeky's Spreadsheet

Current version: 49 (May 12th, 2008)

Excel: http://www.havoc-guild.us/Cheeky/Hunter_DPS_Calc.xls

Open Office: NOTE, no new Open Office version as of right now. Version 45 can be found here: http://www.havoc-guild.us/Cheeky/Hunter_DPS_Calc_OO.xls (Validation fixes for gems and ammo - please note that macros do not currently work correctly in Open Office).
An updated version for Open Office, with macro support, is scheduled for version 50.


Previous three versions: 48, 47, 46
If you find something strange, or something you think has been broken, please check a previous version or two. This might help locate any bugs, or explain why you're seeing what you're seeing (by referring to the change logs).
Previous versions are only available for Excel.



NOTE:
If there's a bug with an item having the incorrect stats, etc, check the list below (post #3 in this thread)! If the item is NOT listed, PM me (Lactose), or post in this thread.



Try keeping this thread focused on the spreadsheet development, or issues related to calculations, etc.
Other areas of discussion are usually better suited in another thread.

SNAKEBALL™

 

◊ Lactose

Step 1: Enter your gear
On the 'Gear' tab you can select for every item/enchant/gem your character currently has. If you find you have an item that is not currently listed, you can enter stats for it in the 'Custom Gear' tab, and then select "Custom" for that slot. The spreadsheet automatically checks for valid gem combinations for socket bonuses and meta gem requirements.

It is also possible to import gear from the armory. This is still fresh, and might have some bugs. It currently does not include enchants.

There are 5 profiles available to use. You can select a profile name, and either save the current gear set up to it, or load in a saved profile. The profiles contain gear, talents, pet specifications, and shot rotations. You can change the name of your profiles on the 'Settings and Results' tab. The buttons marked "Save" and "Load" do just that.

The "CalcAttributes" button is used to refigure the DPS increase of each specific stat. This happens automatically as gear is changed, but not when talents, shots, or pet information changes, or a new profile is loaded. Clicking the button will recompute the results shown on the 'Settings and Results' tab.


Step 2: Enter your talent spec
On the 'Talents' tab you can enter your current talents. Anything with a red background means it has no effect on any of the spreadsheet's calculations. These can be filled in purely to track the information for you. There is no enforcement of total talent points or their distribution. A "Reset" button is provided to clear all talents back to 0.


Step 3: Enter Shot Rotations
This is the most complicated part of using the spreadsheet, but done in a systematic manner isn't too hard to deal with. There are 4 Shot Rotations that can be specified. They are as follows:

• Normal haste. This one is found on the 'Shot Rotations' tab, and bases timing off of the static haste and weapon speed your character has. On this sheet you can also specify your latency (or reaction time if you hand-time shots) and what Aspect you want to be in. There are buttons provided to assist in building standard rotations (please note that these do currently not work for Open Office users):
  • Reset - clear all shots to None. This is best used when building your own shot rotation from scratch.
  • AutoOnly - sets every shot to Auto Shot.
  • 1:1 (Steady) - A standard Auto, Steady repeat rotation.
  • 1:1 (Priority) - A standard 1:1 rotation where you can assign different specials to be prioritized if they are off cooldown.
  • 1:1.X - This allows you to quickly build a shot rotation that will factor in 2 specials between autos as appropriate. You can indicate whether you want Arcane, Multi, or both included. If both are selected it picks the highest damaging shot if both are available at the same time.
• Quick Shots. This is located on the 'Quick Shots' tab. In all respects it is identical to what you find on the normal 'Shot Rotation' tab. Latency and Aspect are used based on the values set on the 'Shot Rotation' tab. (If you do not use Aspect of the Hawk, or have no points in Improved Aspect of the Hawk this entire rotation doesn't matter.)
• Dragonspine Trophy. There are 2 rotations available on the 'Dragonspine Trophy' tab. One is for the DST proc without Quick Shots active, and the other is for both Quick Shots and DST proc active simultaneously. The Dragonspine Trophy's effect is modeled by determining your time spent in different haste states, and the damage you do while they are active. If you do not have a Dragonspine Trophy equipped you can ignore this tab.

Step 4: Enter Pet data
The 'Pet' tab allows you to pick your pet's type (Cat, Ravager, etc.) and assign training points to all possible abilities. You get warnings if you pick abilities not applicable to your pet, or if you exceed the total number of training points available. You can also select a mood and loyalty level for your pet.

There is a "pet bar" feature where you can prioritize your pet's abilities based on how you want them to consume focus. It displays average cast times for each ability based on available focus, the ability cooldown, and the pet's global cooldown (1.5s). This is a handy way to see if Bite + Claw is better than Claw or compare other abilities head to head.


Step 5: Check results
The 'Settings and Results' tab provides summaries of a bunch of different information. Most of it should be self-explanatory. There is also a DPS equivalency matrix comparing different statistics. This is useful in determining which next item will provide the most "bang for the buck."

As you modify any value in the spreadsheet the results will update automatically. You also can see summary information from your saved profiles. Clicking in the profile name box allows you to rename it. There are also control setting to average out some talents, and to control the Dragonspine Trophy mechanics.

The "Export" button creates a file in your "My Documents" directory called CheekyHunterDPS.log that contains the data of all 5 of your profiles. The "Import" button loads this file into the spreadsheet. You will still need to use the "Load" button on the 'Gear' tab to bring the profile data into the active set of gear, talents, pet, and shot rotations. These files will be supported in future spreadsheet versions.

SNAKEBALL™

 

◊ Lactose

If you notice an item having incorrect stats (or anything similar to this), check the following list.
If the item/ability/etc is not listed here, either PM me or post in this thread about it.


MISSING

• Apr 08: Debuff: Blood Frenzy -- +4% physical damage
• Apr 08: Buff (Hunter/Pet): Inspiring Presence (Not self) -- +1% Hit (Draenei aura).
• Apr 10: [Alembic of Infernal Power]
• May 01: [Elixir of Demonslaying] -- Will affect Ranged Attack Power in 2.4.2.
• May 01: [Righteous Weapon Coating] -- 10 second duration, 45 second internal cooldown, ~15% proc rate.
• May 01: [Adamantite Sharpening Stone] -- Affects Auto Shot, Multi-Shot and Aimed Shot.
• May 01: [Adamantite Weightstone] -- Affects Auto Shot, Multi-Shot and Aimed Shot.
• May 06: [Sun-forged Cleaver]

INCORRECT STATS

Please do not report [Thor'idal, the Stars' Fury] as having incorrect stats. This item will not be updated until one has been seen in-game.



SPELLING ERRORS



MECHANICS

• Sep 26: [Relentless Earthstorm Diamond] - Mechanics changed. Lactose: Might this also affect Slaying Talents? I'll take a look at this.
• Mar 18: Pet Skill: Wind Serpent - Lightning Breath calculations possibly incorrect. Lactose: Testing.

SPREADSHEET

• Mar 18: [Battlemaster's Cruelty] -- Error message when importing.
• Apr 08: Load/Save buffs buttons do not save custom Pet AP buffs.
• Apr 08: Custom gear: Trinket slots not available.
• Apr 08: Possibility of 2 scrolls for pet (Agility and Strength).
• Apr 08: Custom gear: "Haste" not a socket bonus option.
• May 12: [Item 'blackened naaru sliver' not found!]'s proc not modelled. Reference.
  
  

FEATURE REQUESTS

• Apr 08: Option to authenticate to a proxy server when reading gear/talents from Armory. Click me.

PLANNED

• Better Lightning Breath modelling.
• Some new buffs to be added.

SNAKEBALL™

 

◊ Lactose

Version 49:

• Corrected the proc duration of the [Abacus of Violent Odds] from 20 seconds to 10 seconds.
• Fixed the DPS gains from the [Darkmoon Card: Wrath] and [Darkmoon Card: Crusade].
• Fixed cell permissions on DPS per attribute to allow for CalcAttributes to work again.
• Corrected the lookup of on-use +AP procs. This should fix some errors with these trinkets.
• Corrected the lookup of Multi-Shot damage bonus with PvP gloves. This was probably leading to N/A issues for Multi-Shot damage.
• Fixed set bonuses for weapons, shoulders, and gloves.

Version 48:

• Added new enchants (Surefooted, Restore Mana Prime, Boar's Speed, Ring enchants now actually work.)
• Begun adding a gear planner feature, sorting items by DPS and availability. Haste and procs may be a little screwy. Melee weapons are broken down into one-handed and two-handed categories. Ranged weapons are not supported.
• Cleaned up items supported to only be unique items in TBC content.
• Redid +1 stat calculations. This should operate much smoother. It will also generate a "haste cap" where haste is no longer beenficial, and a lookup table of haste -> DPS. This is used to more accurately judge the future value of haste.
• Provide value of hit based off of have 0 +hit gear (useful for item DPS comparisons.)
• Fixed problems with counting the proc of [Madness of the Betrayer] properly.
• Simplified a lot of the code in the spreadsheet (I started treating this like a real software project). This should result in less bugs and faster turn-around times on fixes.
• Completed new model of the [Dragonspine Trophy]. You can also see it's uptimes now if don't have it equipped.
• Changed [Madness of the Betrayer] model to be PPM with differing rates of proc on Auto Shots and Special Shots. This lowers the effective uptime.
• Automate the process of build Main/Off hand weapon lists. This should eliminate future bugs in the lists not being accurate with the master list.
• Changed formatting on the gear page so that "0" values are in white text.
• Added new gear ([Assassin's Alchemist Stone], [Figurine - Khorium Boar], [Claw of Molten Fury], [Fist of Molten Fury])
• Corrected some item stats ([Grimgrin Faceguard], [Feltooth Eviscerator], [Leggings of the Pursuit], [Scaled Drakeskin Chestguard])
• Corrected some spelling mistakes in item names ([The Blade of Harbingers], [Shard-bound Bracers])
• A better model for the [Shattered Sun Pendant of Might] (Scryer) is being used. 16.7% chance of proc after the cooldown, and using ranged critical hit chances to critically hit for double damage.

Version 47:
- Armory scraping for Gear now correctly pulls most enchants too. Thanks to Drakier of Uther for providing the framework and the current set of supported enchants.
- Corrected some item stats ([Band of Ruinous Delight], [Hauberk of the War Bringer], [Tunic of the Ranger Lord]).
- Corrected some item names ([Scale Leggings of the Skirmisher]).
- Added some new items ([Bracers of the Forest Stalker], [Figurine - Shadowsong Panther], [Cloak of the Coming Night], [Sunrage Treads], [Stalker's Chain Armor], [Stalker's Chain Helm], [Stalker's Chain Leggings], [Stalker's Chain Spaulders], [Stalker's Chain Gauntlets], [Assassin's Alchemist Stone], [Leggings of the Betrayed], [Band of Celerity]).
- Modeled the Aldor [Shattered Sun Pendant of Might] similar to other, similar trinkets (45 second internal cool down, 10% chance on hit.)
- Allow for selection of Aldor/Scryer factions (only applies to [Shattered Sun Pendant of Might]) on the 'Settings and Results' tab.

I'll be out of town next week on business. Please send all bug reports to Lactose.

Next up:
Gear sorting based on DPS (starting to put infrastructure in place)
Better modeling for PPM-based items
Better model for Scryer [Shattered Sun Pendant of Might] proc, once we have more data.
Various bug fixes (like auto calculation of stat->DPS changes on gear updates.)


Version 46:
- Fixed some item stats ([Ar'tor's Mainstay], [Gronnstalker's Boots], [Gronnstalker's Belt], [Gronnstalker's Bracers]).
- Added new gear ([Belt of the Silent Path], [Blade of Serration], [Swift Blade of Uncertainty], [The Mutilator], [Trousers of the Scryers' Retainer], [Tunic of the Dark Hour], [Vanir's Left Fist of Brutality], [Vanir's Left Fist of Savagery], [Vanir's Right Fist of Brutality], [Mantle of the Golden Forest], [Band of Ruinous Delight], [Sin'dorei Pendant of Triumph]).
- Fixed some item names ([Choker of Bloodied Feathers], [Harbinger of Doom]).
- Raptor pets now consider Dash a valid skill.
- Added Judgment of the Crusader to Buffs tab.
- Put Lightning Breath on a 2-roll system.



Comments, criticism, ideas and insults are always welcome.

SNAKEBALL™

 

Cheeky

* Moved this from other thread when I saw spreadsheets were branching off *

My spreadsheet I linked earlier in this thread (Worksheet) allows for each person to set up their own shot cycle, so it can be tailored to their weapon speed.

In my latest version most of the Survival talents have been modeled. Yep, they still seem very underwhelming, but they are there.

Still missing a lot of base statistics for different race/level combinations, and the crit/hit/agility ratios for levels 68 & 69 (I'm level 67, everything up to then seems accurate.)

If you have some free time, please take a look at this and let me know if you find it useful. It's mostly done just as I have discovered things that would be useful to me, but I wouldn't mind adding features for other people too. If you find any gear, enchants, or gems I'm missing please let me know to, and I'll get them added.

I've been told it's pretty confusing. There are instructions on the first tab for what the other tabs are used for. Also all gear is set up for what my character is currently wearing, so change out what you need.

Thanks

Cheeky of Havoc

 

◊ Lactose

How do the results you get from the formulae compare to simulation results?

SNAKEBALL™

 

Cheeky

To be honest, I'm not 100% sure. I'm calculating them using the (1 - (chance of not)^(number attempts)) math and then averaging their results over that. I use the shot rotation page to compute the number of shots/second the Hunter is doing for their rotation.

I know it's not going to be as exact as a simulator (mostly because mine assumes a steady state, so even your first shot is getting some benefit) but it should be a close enough approximation to see what the benefit it. Also on Master Tactician I don't have any logic in there for increased chance of re-proc due to the increase crit chance while it is active.

If you have better ideas on how to model these I'd love to hear them.

Cheeky of Havoc

 

• Glaurong

You can use these tables/equations for Hawk. Just ran my simulator with only auto shot for a million second run.

It computes auto shot delay by checking for any haste effects when a shot fires, then computing when the next shot will go off. As far as I can tell that is exactly how it works in game.

No Haste Effects
15% Quiver
15% Quiver and Serpent's Swiftness
If you plug those into excel as separate data sets and plot them it looks like this:


If you plug all those into excel, plot them and add a power trend line it looks like this:


Edit: forgot a negative sign
I would feel confident using that formula for hawk uptime calculations: y = 0.777x^-0.7197

Where X is your weapon speed after permanent hastes and Y is your hawk uptime.

Log Parser for BM Hunters (Right click, save as) - Updated 10/11/2007

 

Vec

I was just wondering if you were planning on putting something similar to Blessing of Kings in the Buffs section.

 

Lodi

It would be nice to be able to compare rapid fire (+quickshot if you have it) dps (which will be too fast to weave steady unless you chose a ridiculously slow weapon) to not using rapid fire, or chosing to waste (most of) the benefit by still weaving steady regardless.

That is, maybe when your between autoshots time falls between 1-1.5 sec, it's better to steady (and auto) every 1.5 sec, but when it's <1 sec, you do better with just arcane+auto (multi would slow the auto as well) ... also keeping in mind that with rapid fire on, quickshot is likely to refresh itself toward the end as well.

I've considered *not* taking imp aoth just so I don't have to adapt so much.

If switching bows specifically for rapid fire or quickshot, the ideal time to do this is immediately after an autoshot fires, right?

 

Cheeky

Cool, I'm not the only one who saw a stair-step function when they did this.

I like this idea better than what I did, where I just figured in extra % damage from the uptime.

So if I were to model this, would I want to just reduce the cooldown of autoshot, and the casting time of Steady by the uptime*bonus? I think that would be the best way to "average" out the effects, which is what I'd be looking for in a spreadsheet.

Did this test get performed with a full 5 ranks in Improved Aspect of the Hawk? I only ask because there are different "steps" I've found with lower ranks. And slightly different amounts of "proc within a proc" due to the extra shot at different weapon speeds. I'm pretty sure that would all be noise though.

I'm going to compare these results to what I currently have in my spreadsheet and see how close we are. I like your methodology better, and the fact that you regressed it to a single equation that's pretty accurate is great!

Cheeky of Havoc

 

• Glaurong

Yeah, test was with all 5 ranks of IAotH. It doesn't really make sense to me to get less than 5/5 if you are going to get it, since each point builds on the last. Although I can see why you would want to model that for completeness sake.

Using the same methods, for 4/5 IAotH I get: y = 0.7721x^-0.7275

So there is a change, albeit a minor one. If I have time later I might run it across all ranks.

That is correct.

Log Parser for BM Hunters (Right click, save as) - Updated 10/11/2007

 

Lodi

Lactose, I didn't catch anything about modelling the interference from using arcane and multi (to both steady, and even the autoshot on a quite-fast weapon).

 

• Glaurong

Numbers for other ranks of IAotH

5/5 : y = 0.777x^-0.7197
4/5 : y = 0.7721x^-0.7275
3/5 : y = 0.7683x^-0.7192
2/5 : y = 0.7608x^-0.7149
1/6 : y = 0.7568x^-0.7153

So if you really want to generalize it you can use:

Y = (-0.0052 * R + 0.7825) * X ^ (0.0021 * R - 0.7257)

Where Y is uptime, R is the rank and X is your permanently hasted weapon speed in seconds.

Log Parser for BM Hunters (Right click, save as) - Updated 10/11/2007

 

shookers

It would be nice if you could include the relative value of Hit Rating as compared to Agility. As of now I've just been assuming it has approximately the same value as Crit Rating, yet I know that cannot be accurate.

 

Arganise

HICS 2.0 has always been my most used tool when it came to optimizing my hunter, so I'm excited to see another spreadsheet for the expansion. :)

I've been messsing around with it abit and I stumbled upon a few questions (2 so far xD)

First of all, is the weapon skill rating working? Doesn't seem to effect DPS if I play with the numbers there.

Secondly, I'm quite confused with how the Shots fields work (i.e. Seconds between Auto Shots). I have some idea, but it would help alot if you could explain abit how to use it properly. :)

Thanks. ^^

 

Auxilium