WotLK Pet Tanking Spreadsheet

Pet Tanking Spreadsheet: Based on Shandara's DPS Spreadsheet, modified by Durante
Created in Microsoft Excel 2003. There will not be an Open Office version.
Beta v.3: http://www.filefront.com/13951045/undefined

Contains additional gear, buffs, debuffs, mitigation calculations, TPS calculations, display layouts, and also contains some fixes in modeling of hunter and pet armor, and pet stamina and health.

The week or so I've been working a modifying a copy of the WotLK DPS Spreadsheet created by Shandara and formerly Cheeky, found at the Elitist Jerks forum. Shandara kindly provided me with an unlocked copy of the spreadsheet with which to work on. It contains some placeholder values and equations, bugs, errors, and not yet implemented features. A list of most of these is found on the To Do sheet in the spreadsheet.

Be warned, no cells in this copy of the spreadsheet are locked in any way, and its very easy to break or change things by erasing or changing the wrong cells. I'd advise if you familiarize yourself with the original DPS spreadsheet found on the Elitist Jerks forum before fiddling with this one. Also, my knowledge and experience with Excel is not nearly as advanced as Shandara's, so I will be very limited in my ability to troubleshoot any errors; I'm still trying to make sense of this monster myself.

That being said, I'd appreciate people testing different talent, pet, gear, and buff combinations to make sure the spreadsheet is modeling health, armor, and avoidance properly. Health and armor I need to fiddle with still in terms of rounding, so expect those values to be anywhere from 1-15 or so off. For those with advanced knowledge of Excel or those good with theory crafting I'd appreciate looking over the Pet Calculations page where I've given a peach colored heading to the areas that I've added or changed, and also looking over the bugs listed on the To Do sheet. Unfortunately I haven't done a very good job documenting all the changes on other pages.

Other bugs or errors not listed on the To Do page:
Fix "Clear Buffs" button to clear new buffs.
Rare runtime error(s). Possibly an error from my machine and not the spreadsheet itself. Please note if you experience this.

Before listing any bugs or errors you find, please check the To Do sheet in the spreadsheet and this post to make sure its not already listed.

Also, please refrain from posting this elsewhere, at least until I get a fully functional version up and running.

Beta version posted. Although a few features are not yet implemented and bugs remain, this version is mostly functional.

On your todo list is pet stam rounding, so here's how you can make it exactly what i have come up with:


All in the Pet Calc tab:
L56 should be: =FLOOR(L55*0.45*(1+(0.2*Pet!M22)),1)
L61 should be: =FLOOR((ToPetStam+PetStamBuffs)*(1+L58)*(1+L59)*(1+L60),1) + FLOOR((PetBaseStam)*(1+L58)*(1+L59)*(1+L60),1)
L68 should be: =FLOOR((PetHealthFromStam+PetBaseHealth+PetHealthFromBuffs)*(1+0.02*Talents!E3),1)

gona keep looking and see if there is anything else i can do to help

2 things for the armor Calc:

F77 should be: =FLOOR(F73*F74*F75*F76,1)

The talent thick hide is calculated on the Hunter calc page in the original file but you dont reference that hunter armor you reference the hunter armor on the gear page which see's no benifit from Thick Hide.

That seemed to do the trick, thanks Zwicky. =)

Almost all of the big issues I have left have to deal with revolve around the programmable embedded buttons. Frankly how they work is a total mystery to me. If anyone knows how to edit the programming or even the size/positioning of the buttons themselves I'd appreciate it.

Also nice catch on Thick Hide. The hunter armor calculations present on Shandara's DPS Spreadsheet are far from comprehensive, and I incorrectly assumed that Thick Hide had been implemented.

Sadly Durante I do not know that much about macro's in Excel, I might ask my work boss about them cause he is a whiz at this stuff. I'll see if i can think up a work related question to start the conversation.

On another note i think you missed one digit in the formula for armor mitigation

=C71/(C71+(467.5*TargetLevel - 22167.5))

I'll keep lookin over stuff.

I know Durante, Shandara's and Cheeky's spreedsheets have always been very quick to make changes that affect DPS but rarely show any thought over other aspects of the hunter like armor / avoidance / pet non DPS stuff.

I know what a huge undertaking you are doing here and there is a reason i didn't take this effort on. I will be here to help however i can tho.

And I thank you very much for this.

Great work guys, let me know if i can help out in anyway, testing or whatever.

Also, I may be able to help you with the macros, may not. Let me know what youre trying to do with them.

Here's a quick rundown of what needs to be fixed.

Cell T34 on the buffs page needs to be fixed. I'm not sure why this isn't working currently as I'm not terribly experienced with the VLOOKUP function, everywhere that I've used it I've mostly been adapting it from its uses elsewhere without fully understanding how it works. The T31 cell is cleared in my current version.

Austere Earthsiege Diamond causes a load of errors when selected in a meta slot. I'm also having issues with new weapons and trinkets that I've added not properly appearing on the gear planner correctly. I suspect both of these have similar root problems. With most pieces of gear, adding them to the spreadsheet is as simple as opening their sheet, adding a new row, plugging in the stats, and dragging down the calculations. Gems, Ranged and melee weapons however use lists of some kind that the Sort buttons use differently in some way, and apparently its not just as simple as adding new items to these lists.

Also on the new trinkets I've added, simply dragging the DPS calculation cells is causing some weird numbers whereas this worked just fine with all other types of armor.

Im starting to look over stuff and understand how it all works, I'll try to help you out with above problems.

Observations so far:
Is the ap threshold for growl 3000k? i thought it was around 2200?

Also, should we add in threat from silverback?



VLOOKUP works as a table lookup to return a value.

VLOOKUP(Value im looking for, Table/Range to look in, Column to retrieve, exact match?)

it looks in the left most column for your value.

Example

VLOOKUP(100,A2:C5,2,TRUE) would return 125
VLOOKUP(190,A2:C5,3,TRUE) would return 17
VLOOKUP(100,A2:C5,2,False) would return n/a

and

VLOOKUP(125,B2:C5,2,TRUE) would return 15

Attack Power	Threat	DPS
50	75	14
100	125	15
150	175	16
200	225	17

I can get that cell working by simply substituting the range of the flasks table below, but a better fix would be to figure out why its not registering the whole table. Did you add to the flaks stats table? If so we have to figure out how to expand the definition for the 'FlaskStats' table, but i havent figured out yet where that is defined.

Ok i figured out where its defined, the field just to the left of the formula bar shows the name of a cell, if you expand that box, you can see other table definitions.

FlaskStats doesnt inlclude the resist column, and needs to be expanded, not sure how just yet.

Ok i figured it out

You need to rename FlaskStats, because that tells VLOOKUP where to search for the resist value for the SelectedFlask.

Currently in your sheet flask stas doesnt include the resist columns

to fix this go to

Insert -> Name -> Define

Select FlaskStats and click on the 'define' button to the right of the defined range (at the bottom of the dialog) This will take you to the range and you can define a new one that includes the resist column.

I suspect that the items that you have added are causing a similar problem, ill look into it

The 3k AP Threshold is just a placeholder until we determine the actual value. Some preliminary testing I did seemed to point to it being somewhere in the 2.8 region if I recall, but much more thorough testing is needed.

I'll likely add threat from Silverback as soon as we have a firm understanding of the threat from it, although I suspect its probably just the normal healing threat of Threat = Effective healing/2.

For the Flask table, that's in the original spreadsheet. All I did was add the Lesser Flask of Resistance, added its stats and visually expanded the table to the right with the Resist column, but I myself don't know how to expand it as far as VLOOKUP is concerned.

Edit: You posted again while I wrote this. Nice find, the flask is working perfectly now.
Edit 2: This was indeed the same issue for the new meta gem. Hopefully the other issues can be solved in the same manor.

for the other items Im trying to track things down, where did you add them if you did?

It seems for gear its looking up pages with the items stats, but I can't figure out where they are referencing, i think its different than the other method, im not sure

concerning macros, the sheet has a ton of them, if you need me to modify the behavior of some of them i could probably do it, i have experience with that. If you need a brand new macro just let me know what you want it to do and ill see if i can pull it off.

Beta v.2 posted.

Added correct rounding to pet HP and armor calculations. Implimented Thick Hide's contribution to hunter armor. Fixed typo in armor mitigation %. Added Resistance to the flasks table. Implimented bonus pet armor and stam from Leggins of Beast Mastery. Slight rearrangement of the pet stamina calculation table.

All the new gear I've added should properly be included in the item type's chart, however its still not fixing the issue with calculating the items DPS contribution and then displaying properly on the gear planner. I'm guessing this is something that has to be straightened out in the macros themselves, I'm searching my guild for someone who's competent in using macros in Excel.

Ihlos wrote:for the other items Im trying to track things down, where did you add them if you did?

It seems for gear its looking up pages with the items stats, but I can't figure out where they are referencing, i think its different than the other method, im not sure

concerning macros, the sheet has a ton of them, if you need me to modify the behavior of some of them i could probably do it, i have experience with that. If you need a brand new macro just let me know what you want it to do and ill see if i can pull it off.

To get to the gear stats, Format > Sheet > Unhide then select the appropriate slot for the type of gear you're looking for.

I've added the Rift-Stalker Armor, Frost resist gear, Leggings of Beast Mastery, several Furious Gladiator Pieces, and several stamina/armor trinkets, Austere meta gem, and a head/shoulder enchant or two. I think that's everything, but there might have been more. They seem to be implemented just fine as far as equiping them with the gear sheet and loading them off the armory, but the gear planner doesn't recognize them properly.

Is the spreadsheet still available? The link does not work for me.

Uploaded the spreadsheet to my filefront, so it shouldn't randomly drop link it did on the other host.

Unfortunately I've hit two pretty significant roadblocks. The first is the formula for Growl. Until I can find a way to get more accurate threat information its going to be extremely difficult to impossible to get a solid formula nailed down. I was using a macro that would cast growl and then stop pet attacks on the same button press, but the pet would still usually sneak an attack in there. Running Omen I'd get a total of how much threat the pet had on the mob, then subtract the total health of the mob since pets as far as we know have a 1:1 damage:threat relationship like normal players, then divide by the number of Growls cast in order to get an average value. Unfortunately I couldn't even get a consistent average threat value for Growl even though I was sure to test on the same mob, mob level, and remove any gear that had any sort of proc that might affect RAP. Being able to accurately measure threat would allow me to model Thunderstomp, Intimidate, and Silverback threat as well.

Secondly my expertise with Excel stops short of being able to edit the myriad of macros found in the spreadsheet. While they still function well enough for the spreadsheet to maintain its base functionality, I'd really like to get things like the gear planner working correctly again especially as I had planned to add options to sort by stamina, armor, AP, hit. Even more ambitious I had hoped to implement an net Stamina and AP gain/loss ratio setting, so that if you wanted to swap out stamina for more threat, you could sort by which items increase AP the most for the smallest decrease in stamina, and vise versa.

That is exactly the same problem i was running into when i was looking numbers for the growl formula. While i was able to get them to be around the same number, after i would subtract off the dmg from the attack, it wasn't a consistant number. I seem to remember it being within ~200 of each other, but again its not that accurate.

I tell you what, ill look into threat and see if i can manage to find a way to measure it.


Also, durante, tell me which macros you want to change and what you want them to do differently and i'll see what I can do.

Lets just start off with one macro and see if i can manage to successfully manipulate it and go from there.

Alas, here is the problem with asking for help with the macros. I'm so clueless as to how they work in Excel that I don't even really know which one does what, or which one is connected to what button other than just looking at the names of the macros and going from there. For example, I feel pretty safe assuming the macro DoClearBuffs is the programming for the "Clear Buffs" button on the buff page, and OrderGems is the "Sort Gems" button on the Settings sheet. Beyond that the whole thing is gypsy magic to me.

Basically what's broken is that none of the items that I added Show up correctly on the gear planner correctly. Take the legs I added for example, Rift Stalkers Leggings and Leggings of Beast Mastery. I think I added them to the Legs table correctly, and they seem to have their DPS calculated on that sheet, but they both show up on the gear planner as None with a blank for their Delta DPS and Location. I don't know if this is something that needs to be fixed with macros or with how I added the items to their respective sheets.

im on the job. ill look at this problem and see if i can't figure out whats going on.

Also if ill see if i can drop you a few hints to at least understanding how to figure out what does what.

Ok the reason that the gear isnt showing up on the gear planner is pretty simple.

The way this works is as follows, lets take leggings as an example, so unhide the leggings worksheet.

Now go to the gear planning sheet and click on one of the legs. The formula for that cell will look something like this.

=IF(Legs!U6=1,Legs!A6,"None")

Thats saying that IF the cell U6 on the sheet "Legs" is '1',
then this cell's value is the value in A6 of legs,
otherwise the value of this cell is 'none'.

It looks like you forced the value on the riftstalker leggings to be 'yes'. Even though it looks like everything else, 'yes' is not '1'. the other 'yes's are actually 1.
Yay for excel making things "easier" for us.

So you could just put '1' instead of yes. The sheet will change it back to 'yes' but if you click on the cell the value will still show as '1'. However you could also make a formula that only allows it to be shown when the box "pre woltlk" is clicked, and this would be more in keeping with the design of the sheet.

For example, the following formula is for valorous crypstalker, and only counts the peice as valid if "woltk", "25 man", and "naxxramas" are selected.

=IF(AND(WotLKValid=TRUE,XXVMan=TRUE,NaxxValid=TRUE),1,0)

Now, the issue here is that after making your option selections, the "sort" button will sort the peices based on PURE DPS gain and display them. It does this by running a sort macro on every page, including our legs page.

When this happens, riftstalker leggings will float down to the bottom since clearly they are not good enough to be in the top 10 dps items.


There are few ways we could handle this. We could rewrite the sort function to sort based on stam, rap, hit, crit, armor, and int instead of dps. We would need a weight scale for this.

Using gems and the LW enchants, I think we could balance AP and stam against each other (item level perspective) and then balance them against hit and armor. Basically we need a complete weighting system. Different hunters may not want to focus on stam/armor as much as threat, so there should be some way to select your weighting scheme.

The other way to do this is to disable the sorting button, set up the 'legs' worksheet with the pieces in the order that we believe is best, making the gear planner basically a filtered list of our master list.

Again this wont be easy because how do you compare t5 bonus with current gear? where does it rank?

The best way to do it would be a hybrid of the two
Get a weight scale, and assign each peice a 'tanking value'. replace the "total dps" column with this number and VIOLA, the sort function will work perfectly.

All we have to do is come up with a weight scale that appropriately factors survivability and threat, and maybe a way for the user to switch the scale.

Such as "I want 40% survivability and 60% threat"
Or maybe three options
-Perfect balance
-Survivabilty focus
-Threat focus

Am i making any sense? i hope so.

Ok so my last post was kind of a 'stream of consciousness' post where i just typed as i thought.

Basically what I was trying to say is that what we need to do is make a weight scale to give all of the stats a weight, to determine a numerical repesentation of the worth of an item for tanking.

Once we have that we can lay that value in over the dps calculation and all the functions of the gear planner will fall into line perfectly.

In keeping with the spirit of the sheet (it is a personal tool for experimenting and maximizing ones dps), we should keep in mind a few things.

-Users should be allowed to input their own weight. We should probably have some default settings to choose from. This can be a box on the gear planner with a button that recalculates the values on the gear sheets.

-Calculations should take into effect the user's choices in other parts of the spreadsheet. For example, we should check to see if they have the int->AP talent, are they hit capped? do the buffs they have change which peice would be more appropriate? (such as kings).

I will concede that in theory a weight scale would already take these things into effect. It all depends on how we calculate stuff.


I will say this, getting the spreadsheet to full working capacity will not only be a great excercise for us to make sure we know how the mechanics of the class apply to pet tanking, but it will also lend us credibility as serious theorycrafters, and it will help other pet tankers make informed decisions and strategies.

Im just glad to be helping out with it!