Tanking Spreadsheet - Page 5

Taliafears · 04/03/08, 1:58 PM

Uploaded a new release. Fixes for the ZA helm, Eternal Earthstorm Diamond, and Hard Khorium Goggles. Also a fix for BV stat value with Shield Mastery (it was calculating lower than it should have been).

Valdinar · 04/03/08, 8:04 PM

Great thread and spreadsheet! One thing to add:

- Enchant cloak 12 defense

ioguolo · 04/11/08, 10:56 PM

For some reason your formula for calculating matches color for GemChoices doesn't work with my excel. I had to change where the iserror = 0 to iserror = false.

For Cell M8

=IF(AND(B8="Blue",ISERROR(MATCH(L8,BlueGems,0))=FALSE),1,0)+IF(AND(B8="Yellow",ISERROR(MATCH(L8,YellowGems,0))=FALSE),1,0)+IF(AND(B8="Red",ISERROR(MATCH(L8,RedGems,0))=FALSE),1,0)+IF(A8="",1,0)

I have Office 2007 btw. If I'm missing something the above fix could be wrong, but it's the only way I could get the values to read correctly.

Taliafears · 04/13/08, 9:57 AM

Interesting, thanks for the report. I'll try FALSE and see if it breaks OpenOffice.

ioguolo · 04/14/08, 1:35 PM

Originally Posted by Taliafears

Interesting, thanks for the report. I'll try FALSE and see if it breaks OpenOffice.

np, bump on steelweave enchant too

ezweb · 04/14/08, 2:40 PM

In the armorwarr20080403 version, it isn't counting stamina socket bonuses on the Fitting Room page, and thus the Stats page has the wrong total health.

• Erlaya · 04/15/08, 3:06 AM

Anyway next release could add in enchant rings with +4 stats option?

b1mmer · 04/17/08, 12:23 PM

Awesome! Been looking for tanking spread sheet for a long time.
Few questions thought.
- The gem selection is very limited. No Rigid Dawnstone (+hit)
- Is it ok if I just insert new row and put new gem with stats in there just like others? Will it break any calculations inside the table?

Taliafears · 04/17/08, 9:33 PM

Adding hit gems to the gem sheet will be more complicated than that because I only allowed for a few stats on gems. I'll try to expand it to include more stats.

Killerk · 04/18/08, 10:16 AM

Originally Posted by ioguolo

For some reason your formula for calculating matches color for GemChoices doesn't work with my excel. I had to change where the iserror = 0 to iserror = false.

For Cell M8

=IF(AND(B8="Blue",ISERROR(MATCH(L8,BlueGems,0))=FALSE),1,0)+IF(AND(B8="Yellow",ISERROR(MATCH(L8,YellowGems,0))=FALSE),1,0)+IF(AND(B8="Red",ISERROR(MATCH(L8,RedGems,0))=FALSE),1,0)+IF(A8="",1,0)

I have Office 2007 btw. If I'm missing something the above fix could be wrong, but it's the only way I could get the values to read correctly.

I had simlar trouble, the fix above works and was applied to all non-totaling rows in column M and I have Office 2003.

Nice spreadsheet btw.

Mode · 04/19/08, 1:19 PM

Excellent sheet. A few suggestions:

-Add the PVP ranged weapons. They're pretty sweet.

-Edit: Are the procs on SSO necks modeled? Do you plan to add aldor vs scryer?

Extra-extra edit: I am an idiot who cannot read to save his life or gold.

Thuryn · 04/23/08, 10:17 AM

Just a quick item I noticed.

Fitting Room tab, cell c32 ignores the AEP from the gems. Cell c33 ignores the same, and also misses a few of the buff AEP values.

Assuming that was an oversight, I would suggest:

C32 change to =SUM(C2:C30)

C33 change to =SUM(C2:C30)+SUM(C36:C53)+SUM(C55:C68)

I extended the range of the second to C53 to allow room for any additions, which will automatically be captured without changing the forumla.

Hope that helps. Love the spreadsheet, so thank you for all the work you put into it.

Llilyth · 05/03/08, 6:11 PM

Is it possible to configure which gems are considered? For example, I'd like to remove the epic gems and only consider blues.

Thanks for the great spreadsheet!

EDIT: I think I got it. Just edit the name of "Best Gem" in the Gems sheet.

Oderine · 05/03/08, 10:03 PM

I Just notice a small mistake in the Fitting room : the BoK, line 39, do not include gems into the formula. I think it should.

Great spreadsheet otherwise.

Amaelalin · 05/19/08, 6:42 AM

Would you consider adding the Signet of Eternal Life ([Signet of Eternal Life]) ring to your spreadsheet? The ring has 54 stamina (as well as 37 resilience rating for those tanks just short of uncrittable defense).

This is a rather handy spreadsheet - thanks for putting in the effort to build this thing!

Celandro · 05/27/08, 5:31 PM

Please add 'Figurine – Empyrean Tortoise'

Sambuca · 06/30/08, 10:59 PM

Did we let this one die?

Onyki · 07/01/08, 5:10 AM

Originally Posted by Sambuca

Did we let this one die?

I hope not, these informations are invaluables. (Thanks anyway to the OP for making this sheet).

zztuf · 07/20/08, 10:32 AM

Strange, in Excel, for every weapon there is an error in there respective AEP cell.
But with Openoffice, the number is calculated but gives an incredible high value.

According to this spreadsheet, I have a TPS of 979 723 full buffed ans 1 064 351 unbuffed.

It's a nice attemp, but full of bugs and errors.

I don't know at all how to work with excel or openoffice, so I can't fix it myself.

Aieeja · 07/20/08, 2:59 PM

[Ring of Hardened Resolve] is missing :>

jabbarahm · 08/12/08, 5:23 PM

any word on adding brutallus to the list of bosses? mechanics are quite different than morogrim as well as the amount you're being hit for

Taliafears · 09/16/08, 3:45 PM

Hey guys, sorry for the long absence. The sheet was in relatively good shape and I got busy at work, etc etc.

Now that 3.0.2 is on its way, I'm starting to get it updated so we have a clue what to do with ourselves.

I've uploaded a new version of the sheet, here's the changelist:

- starting changes for 3.0.2
- removed all references to crushing and all calculations for 5s shield block rotation dynamics
- reduced Shield Mastery talent point max to 2
- assuming maximum Shield block uptime (10s of every 60s, or 10 of 40 talented)
- added critical block to talents
- added critical block and shield block to average block value
- added atomic wpn dps, wpn speed, and wpn type boxes to gear sheet, for international support
- updated Anticipation to add dodge instead of defense
- changed Shield Spec to reflect chance at 2 rage returned during block
- Defiance changed to Incite, expertise bonus removed
- Vitality reduced to 3 ranks, str bonus removed, expertise bonus added
- changed Threat modifier to static 1.45
- changed max dps statistic to assume no avoidance, no shield block, but able to block proportionally based on block rate
- changed Improved Sunder to Puncture and reduced to 2 ranks
- added Sword and Board Rotation (my quick math shows about 3 full cost and 8 free shield slams per 48s cycle)
- added partial Sword and Board rotations when not fully talented
- added enrage damage from Imp. Def. Stance all over the place
- added Improved Revenge (and damage bonus)
- added critical block shield slam crit bonus
- added Incite heroic strike crit bonus
- added Sword and Board devastate crit bonus
- took away devastate's innate threat bonus (this is a guess)
- fixed total AEP calculations to include gems/more buffs (per Thuryn's post)
- updated Blessing of Kings calculation to include stats from gems (per Oderine's post)
- added Ring of Hardened Resolve and Signet of Eternal Life
- added some Hit gems

Taliafears · 09/17/08, 7:25 PM

A new release of the sheet is on its way, but I wanted to toss up the Sword and Board simulator I've been working on in case anybody else wants to pound on it.

<?php
// optional parameters
$n = 5000000; // number of GCDs in the simulation
$cycle = 120; // length (in seconds) of the period of combat or cycle length
$talent = 1; // number of talent points in sword & board
$userev = 1; // whether to use revenge in your rotation when it's up
$lag = 150; // number of milliseconds to add to the GCD to account for lag

// variables for the simulation
$ss_up = 0;
$ss_free = 0;
$ss_counter = 0;
$rev_counter = 0;

$totals = array(
	'freeslams' => 0,
	'slams' => 0,
	'devas' => 0,
	'revs' => 0
);

// run the simulation
for ($i = 0; $i < $n; $i++) {
	$rev_counter++;
	$ss_counter++;
	if ($ss_up) {
		if ($ss_free) $totals['freeslams']++;
		$totals['slams']++;
		$ss_up = 0;
		$ss_free = 0;
		$ss_counter = 0;
//		echo 'SS<br/>';
	} elseif ($rev_counter > 3 && $userev && mt_rand(0,7) < 6) { // 75% chance for revenge to be lit up
		$totals['revs']++;
		$rev_counter = 0;
//		echo 'Rv';
	} else {
		$totals['devas']++;
		if (mt_rand(0,9) < $talent) {
			$ss_up = 1;
			$ss_free = 1;
		}
//	echo 'Dv';
	}
	if ($ss_counter > 2) {
		$ss_up = 1;
	}
}

// print the results
$factor = $n*(1.5+$lag/1000)/$cycle;
echo 'In a '.$cycle.'s cycle with '.$lag.'ms lag between moves:<br/>';
echo 'Shield Slams: '.sprintf('%.1f',$totals['slams']/$factor).'<br/>';
echo 'Free Slams: '.sprintf('%.1f',$totals['freeslams']/$factor).'<br/>';
echo 'Devastates: '.sprintf('%.1f',$totals['devas']/$factor).'<br/>';
if ($userev) echo 'Revenges: '.sprintf('%.1f',$totals['revs']/$factor).'<br/>';
?>

Taliafears · 09/17/08, 7:42 PM

Uploaded a new version. Here's the changelist:

- removed Shield Slam talent, now always enabled
- updated stats on boots to data from post on mmo-champion (some formerly paladin items need added)
- updated stats on chest pieces (some formerly paladin items need added)
- updated stats on leg pieces (some formerly paladin items need added)
- changed strength -> block value conversion to 2 str == 1 BV
- added strength as a tanking stat for nxtstat calculation
- updated a couple helms, need to do them all
- completed stat alteration pass - all items should match wotlk.wowhead.com (still need to add former paladin items)
- updated Eternal Earthstorm to enhance block value from Strength (need to confirm that it works this way)
- Eternal Earthstorm nerfed to +5% block value
- added Impale, Deep Wounds, and Justified Killing to talent list
- added Impale damage to special abilities
- reduced devastate bonus damage (I had been using the lvl 80 value)
- added rage from Justified Killing
- threat calculation no longer allows more heroic strikes than would be possible with your weapon
- added value calculations next to talents, to make sheet easier to maintain (haven't yet changed everything to reference them)
- changed Revenge to scale with AP as shown on wotlk.wowhead.com
- changed unbuffed mob attack speed to 0.8s to reflect multiple attackers
- removed Grace of Air buff, changed Strength of Earth to add agility
- Blessing of Might no longer stacks with Battle Shout - Battle Shout takes priority
- Trueshot Aura now increases AP by 10% (I have this stacking with Unleashed Rage - does it?)
- changed Windfury to 252 haste rating plus 16% more haste rating from gear (maybe not perfect but close)
- turned on most raid buffs by default -they're raid-wide now
- created a simulator for sword & board rotation, assumed no Revenge and 150ms lag for one move every 1.65s
- simulated adding revenge into the mix, named the cycle RevBoard[1-3]

Taliafears · 09/20/08, 10:47 AM

Uploaded another release, still a good bit to do but we're getting there.

Changelist:
- added back strength bonus from Vitality. I'm sure it had been removed at one point
- Unleashed Rage does not stack with Trueshot Aura, combined these buffs
- Blood Pact no longer stacks with Commanding Shout
- Blood Pact now gives a health buff instead of a stamina buff
- added Gag Order talent, added bonus damage to Shield Slam
- fixed a problem where Shield Bash was doing the same threat as Bloodthirst
- moved heroic strike swing damage to the right with all the other special abilities
- added DPS calculations in addition to TPS
- Shield Bash appears not to deal damage anymore, unknown if innate threat has changed
- Changed some cycles to remove Shield Bash and use Shield Slam
- Humans only get 3 expertise now
- added Armored to the Teeth talent
- Removed the rage bonus of Justified Killing, keeping the talent around in case they change their mind again
- gave Devastate an innate threat bonus of 5% of AP, this MAY be only for the first five applications, will have to keep an eye on it
- changed overall threat modifier from defensive stance to 2.0735 (Blessing of Salvation has been baked into defensive stance)
- made a pass at fixing lots of fields to reference the C column for talents
- took away tauren racial bonus when valuing stam, as it will no longer scale with gear, still unknown what the actual bonus will be
- added Blessing of Kings talent to the Options sheet, as now you need 5 talent points for 10%
- Kings stacks with talents. Not sure if this was a bug before or not; BoK may not stack with Vitality
- added Shield Mastery to the valuation for block value, big buff for block value
- Devastate back down to 50% damage
- fixed a big bug with average DPS-in calculation
- reduced default weight for burst survivability, without crushes I think this is less of an issue
- changed worst case calculation to give you half your block rating, it feels like it's overvaluing block rating currently
- updated stat values for the new calculations, also included intangibles like the need to NOT miss and have 490 defense