Hunter Spreadsheets -- In development

Lactose · 02/07/07, 2:40 PM

Cheeky's Spreadsheet

FINAL version: 52 (July 31st, 2008)

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

Open Office: http://www.havoc-guild.us/Cheeky/Hunter_DPS_Calc.ods
NOTE: Open Office version is currently only available for version 49!
Be sure to have set the appropriate macro settings for the macros to work!
A .zip file will be downloaded. Simply open the .zip file in Open Office.

Cheeky is no longer supporting the spreadsheet. Additionally, I will no longer be maintaining this thread.

Lactose · 02/07/07, 2:41 PM

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.

Lactose · 02/07/07, 2:41 PM

Removed.

Lactose · 02/07/07, 2:42 PM

Version 52:

Unlocked spreadsheet. This will be the final version supported by Cheeky.

Version 51:

(WotLK) Invigorate now only applies to pet special attacks.
(WotLK) Talent point totals are now accurate, and display even when you have more than 10 ranks in a tree.
(WotLK) The damage for Steady Shot is now accurate, and based off of more than target armor.
(WotLK) Arcane Shot's damage uses the correct numbers to calculate.
(WotLK) Began modeling Pet DPS and talents. The following Talents are modeled (Bestial Discipline, Go For the Throat, Animal Handler, Ferocity, Frenzy, Cobra Strikes). The following abilities are modeled (Kill Command, Rabid, Spiked Collar, Cobra Reflexes, Roar of Recovery, Spider's Bite, Owl's Focus, Call of the Wild).
Corrected a bug where the Scryer version of the Shattered Sun Pendant of Might was not being counted towards DPS.
(WotLK) Fixed the Quick Shots uptime calculations.
(WotLK) Modeled focus dumps (Bite/Claw/Smack) for all pets. It is assumed the focus dump will be used as often as possible after talented, focus-using abilities are used.
Reverted Armory scrape code to work with special European letters again.

Version 50:

Gear Planner feature can now be filtered on Leather items, PvP items, and items that require you to have the requisite crafting skill to equip them.
On advice from Cole of the ElitistJerks forums, I had discovered ways to significantly improve performance of most buttons.
Fixed an error where the item set for Chest/Hands items was incorrect.
Removed item set names/support for melee weapons.
Added new gear ([Sun-forged Cleaver], [Vicious Hawkstrider Hauberk], [Thalassian Ranger Gauntlets], [Coif of Alleria], [Cloak of Unforgivable Sin], [Crux of the Apocalypse], [Hand of the Deceiver], [Brutal Gladiator's Chain Armor], [Brutal Gladiator's Chain Gauntlets], [Brutal Gladiator's Chain Helm], [Brutal Gladiator's Chain Leggings], [Brutal Gladiator's Chain Spaulders])
Corrected some item stats ([Thori'dal, the Stars' Fury], [Ancient Amani Longbow], [Darkmoon Card: Wrath])
Corrected some spelling errors ([Stalker's Chain Gauntlets], [Blackened Naaru Sliver], [Insignia of the Mag'hari Hero])
Corrected expected DPS gain for [Dragonspine Trophy]
Corrected a bug where expected DPS for Trinkets was always judging +hit on current values, regardless of setting on Gear Planner tab.
Modeled the proc on the [Blackened Naaru Sliver].
When selecting a Scroll buff for pets, "Both" can be selected which adds +20 Strength and +20 Agility.
[Elixir of Demonslaying] added as a Battle Elixir (only works if target type selected is Demon.)
Corrected bugs in calculating the uptime of the [Hourglass of the Unraveller] and [Tsunami Talisman] procs.

Model for multiple Wrath of the Lich King abilities. These are all preliminary models as feedback is returned from Beta testing. See the WotLK tabs for specifics.
Removed the Intro tab, and combined it with the Change Log tab, added a link to a wonderful how-to on using this spreadsheet

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.

Cheeky · 02/07/07, 4:22 PM

* 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

Lactose · 02/07/07, 4:45 PM

Originally Posted by Cheeky

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

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

Cheeky · 02/07/07, 5:01 PM

Originally Posted by Lactose

Originally Posted by Cheeky

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

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

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.

Glaurong · 02/07/07, 5:22 PM

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

Speed	Hawk Uptime
1.8	0.5175
1.9	0.4984
2	0.48
2.1	0.4619
2.2	0.4463
2.3	0.4335
2.4	0.4205
2.5	0.4081
2.6	0.394
2.7	0.3816
2.8	0.3686
2.9	0.361
3	0.3489
3.1	0.3398
3.2	0.3364
3.3	0.3264
3.4	0.3144

15% Quiver

Speed	Hawk Uptime
1.565	0.5604
1.652	0.54
1.739	0.5199
1.826	0.5046
1.913	0.4867
2	0.479
2.087	0.4626
2.174	0.4523
2.261	0.4312
2.348	0.4176
2.435	0.4055
2.522	0.4009
2.609	0.387
2.696	0.3828
2.783	0.3702
2.87	0.362
2.957	0.3509

15% Quiver and Serpent's Swiftness

1.304	0.6172
1.377	0.6179
1.449	0.5901
1.522	0.5628
1.594	0.5603
1.667	0.538
1.739	0.5158
1.812	0.5041
1.884	0.4975
1.957	0.4776
2.029	0.4653
2.102	0.4514
2.174	0.4494
2.247	0.432
2.319	0.4207
2.392	0.4173
2.464	0.4072

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.

Vec · 02/07/07, 5:39 PM

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

Lodi · 02/07/07, 5:41 PM

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 · 02/07/07, 5:42 PM

Originally Posted by 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.

If you plug all those into excel, plot them and add a power trend line it looks like this:
http://www.vanirguild.com/storage/hawk_uptime.jpg

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.

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!

Glaurong · 02/07/07, 6:21 PM

Did this test get performed with a full 5 ranks in Improved Aspect of the Hawk?

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.

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.

That is correct.

Lodi · 02/07/07, 6:38 PM

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 · 02/07/07, 6:54 PM

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.

shookers · 02/08/07, 1:16 AM

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.