◊ Lactose

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.

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

 

◊ 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.

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

 

◊ Lactose

Removed.

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

 

◊ Lactose

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.

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

 

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?

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

 

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

Back when the first changes were announced I briefly considered *not* taking the Improved Arcane Shot talent. Have you been considering any potentional benefits of a 6 sec Arcane Shot over 5 sec Arcane Shots? Being able to spend 5 points elsewhere might improve overall performance. I know this is not directly related to the spreadsheet, but then it would affect your cycle.

On the subject of Beast Mastery, I would definitely consider implenting a section for pets. Most of the pet DPS is not dependant on hunter actions, it would depend on gear and with Kill Command, you can affect your pets DPS with your ranged weapon. Besides that tho, seeing how viable the tree is and with the announcement of more pet abilities, I would've been interested in being able to calculate pet specific things.

I think you'll know what Talents to include from the BM tree ( :P ) and the other trees and the model will be complete once one could take into account the pet specific ability (Lightning Breath etc.) and Kill Command.

 

◊ Caithen

Enjoying playing this this right now Cheeky, but is there any chance you could post a version where the sheets holding item/enchant data are not hidden/locked, as I would like to use the nice format, rather than a massive user-add row.

 

Cheeky

I'm glad you are enjoying it.

I included some newer gear in the version I posted yesterday afternoon (Version 3). There are a couple of reasons why I have the gear listing hidden:
1 - I'm a bit of a control freak.
2 - By forcing people to tell me what gear/gems/enchants are missing, I learn about them. If anyone could just change it themsleves they probably would, and no on else might benefit (also see #1).
3 - Those pages aren't exactly in the nicest format. And there are a lot of them. I use a separate page for each slot, and 1 for gems. I figured no one really wanted to see all that junk. If you spot a bug in the numbers you see for a specific item, it's easy enough for me to correct and re-post. (also see #1 - there is a pattern here)

I'm hoping to update the spreadsheet every couple of days for new gear. Thottbat and Kaliban's (the 2 sources I've been using) are being updated daily. I don't think even all the items from the heroic 5 mans are in the sheet yet.

If there are sets of gear or specific items you'd like added you can send me a private message here (when they are back up) or at my guild website (Havoc).

I'm also still looking for accurate racial stats and base health/mana for non-Trolls. Right now those are all way off. (I actually think my health totals are off even for Trolls...)

Plugging this into my spreadsheet I've found your numbers work out to be about 20-25% less than what I was using. Yours do "feel" more correct, as well as being based on a more solid method of simulation. I have always thought I was overestimating the impact of a tier 1 talent. My next version will remove that huge lookup table and just substitute in your equation.

Would you like credit for it to be given to "Glaurong of the Elitist Jerks forums" or something else?

Cheeky of Havoc

 

tict

So Lactose thanks for the spreadsheet. Quick question though, as noted above would it be correct to believe that a quick ranged weapon is really that much better then a slow/medium speed one?

The Consortium Blaster is showing to greatly lose out (I am a MM build) to the Wrathfire Handcannon:

Consortium Blaster
111 - 207 Damage Speed 2.40
(66.3 damage per second)
+15 Stamina
Durability 75 / 75
Requires Level 70
Requires The Consortium - Revered
Equip: Improves critical strike rating by 7.
Equip: Increases attack power by 36.

Wrathfire Hand-Cannon
Binds when picked up
Gun90 - 168 Damage Speed 2.00
(64.5 damage per second)
Durability 75 / 75
Requires Level 68
Equip: Improves critical strike rating by 15.
Equip: Increases attack power by 40.

The numbers I am seeing are 830 DPS versus 912 DPS, quite a huge difference IMO.
I was under the impression because I am speced for multi shot dmg, the slower wep would help me do more dmg per MS.

Would it be wise to go with slower weapons in BC now then? Thos Karaz epics included?

Thanks alot.

T

 

Azulor

@Lactose. Apologies If I have the wrong version or am missing something, I'm sort of rushed. But,

1. The formulas for both arcane shot average and multishot average appear incorrect. For example, B176 upon simplification appears to return B175*B151, which is Multishot crit. This explains why changing entries in Improved Barrage have no impact on the final DPS calculations. Likewise, B164 returns B163*B151, basically arcane crit.

2. The logical statements you use to build the raw numbers from row 157 onwards are sensitive to B94 and B97, which are sensitive to B29 and B31 (also the C column for new DPS I suppose). You might want to change the default return on, for example, B97/C97 and B94/C94 to be max rank because as written with no entries in B29/C29 and B31/C31, the arcane and multishot damage gets computed as zero literally (not simply min zero added damage).

Nice work as always.

This should impact the agi/crit/rap tradeoffs (assuming there are indeed errors and I am not missing something).

 

taurenhunter

I would like to make a suggestion: to put names on the relevant Excel columns/cells.

That way, a cryptic formula like
( ( C:C + D:D ) / 2 ) / E:E

would look like
( ( LowDmg + HighDmg ) / 2 ) / Speed

which is a lot easier to understand and troubleshoot.

The option to name a column or cell can be found on Insert > Name > Define or simply use the name box at the upper left corner of the spreadsheet.

I would upload my own spreadsheet to see if you guys would find anything useful but filefront is now under maintenance.
It looks like this
http://spreadsheets.google.com/pub?k...BQNwWO24--JcCA
(old version published on Google - it doesn't show the formulas though).

 

Glaurong

When you do start getting naked stats for the different races remember that the first 10 stamina/intellect do nothing for your heal/mana totals.

Glau of Hyjal is fine and thanks for asking.

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

 

Cheeky

Thanks, I think I was operating under the idea the first 20 was ignored.

No problem, you did the work on that.

Any other advice on bug fixes or features you'd like to see?

Cheeky of Havoc

 

Lodi

cheeky:

auto starts at 0.0, then steady starts at 0.0 ... you're not adding a .5 sec gap for auto to actually fire?