I’m back! After a long vacation and a subsequent holiday, I am back to blogging full time. Although I got one blog in on the couch while my parents watched some football, I really have not had much time for my blog. Well with the holidays gone, and the next one 24 days away, I have a few weeks of solid posts regarding my favorite flavors of the month. The Christmas Tree is up, I am back to work, and I am back to blogging! I wanted to talk a bit about a spreadsheet I have been developing.

## Creating a user friendly interface

After breaking down all of the information that went into Theck’s new formula, it is now time to deal with real world application, sure the calculations and derivations that it took to get to those last three formulas, with all of the variables defined are interesting, however most of us do not see the Matrix as code, we just want something simple and easy to work with to understand if we should swap out a gear set in between fights. This is where I come in. I have begun to create a spreadsheet that the greater community can use to evaluate gear choices, and I wanted to discus it in further detail here so that people know what it is about and have the opportunity to provide feedback before its grand unveiling.

This is my first draft of the UI. It is a basic excel spreadsheet with the formulas already built in. It provides descriptions for all of the fields that you need to fill in and will export the final values to specific fields with in the spreadsheet. The spreadsheet is color coded to allow for a “gear evaluations for dummies” type feel. The pink (because we are paladins) is all of the information that you should get from both your character screen during a raid, as well as WWS, WOL, etc parses. The green cells are values that you must input in order to give you the calculation’s results, and the yellow cells are constants that *MAY* be changed but are pretty standard for normal gearing, raid buffs, and consumables. It is also important to note that I kept the yellow cells visible so that other tank classes could alter those values based on their talents, racial abilities, etc and still use the tool.

I just want to also provide the disclaimer that I am by no means an excel genius. In fact, I am no where near the expert levels of the program, however I do use spreadsheets on a daily basis at my job and I like them to be formatted, user friendly, and overall specific to the task at hand. So please be gentle when critiquing the simplistic nature of the sheet, as it only provides relative information for quick evaluation in between pulls and nothing more.

## How to use the spreadsheet

There are three specific things that this spreadsheet can tell you, and all of them require a bit more interpretation once you have gotten the information that it provides. I would like to go over each of these important pieces of information one by one and tell you what they are and what we can do with our new found information.

Definitions up front:

X – Percent of total damage taken which was from Bleed Damage

Y – Percent of total damage taken which was from Magic Damage

H – Your total raid buffed health value

A – Your total raid buffed armor

Ma – Your total raid buffed reduction in incoming damage from Armor

## Effective Health Calculator

The first and foremost thing that this spreadsheet gives you is an absolute value for your effective health which accounts for physical, bleed, and magic damage. This number is relative to the gear you are wearing, the encounter you are tanking, and all of the talents, consumables, and buffs that you have. When you input all of the values that are required by the spreadsheet, H, A, X, Y, and Ma, you will get a number at the bottom. This is your effective health. This value alone does not really give you much information, but what it does give you is a starting point.

What you should do with this information is compare it with different gear choices, armor and stamina values, as well as resistances to establish mathematically which gear set is the best set for a given encounter. Which ever set gives you the highest effective health value is the one that you should be wearing for a given encounter with set X and Y values. As each encounter has different X and Y values, your evaluations have to be done on a boss by boss basis.

## Change in Stamina and Change in Armor Calcuators

The second thing that the spreadsheet will calculate for you is a change in stamina. We all know that the general rule for the stamina to armor ratio is that 1 stamina is roughly equal to 11.7 armor. However how do we evaluate this ratio with the new understanding of relative X and Y values during a given fight. The relative benefit we see from armor changes as X and Y increase, and this calculator will tell you what change in armor is required to give you the same effective health benefit as a similar change in Stamina. Specifically, if you know the relative X and Y values for a fight, and you want to compare pieces, you can plug in the change in Stamina and Armor of a given slot into the spreadsheet and it will give you relative values of the counter part which would provide the same amount of effective health.

To provide you with an example, If you wanted to check to see if a bonus armor piece of gear was as good as that piece with 15 more stamina, you would input your relative values of X and Y into the spread sheet and then type 15 into the dS (or change in Stamina) cell on the spreadsheet and the spreadsheet would output the relative amount of armor required to equal 15 stamina with respect to effective health. This is a powerful tool when assembling gear sets for progression content, where surviving burst is your most important job.

The third thing that the spreadsheet does is the exact mirror of the evaluation above. It will calculate a change in armor and give you its relative stamina value for any given fight, provided that you input X and Y values. So, you can see how much stamina a given piece of gear has to have to eclipse the bonus armor that you have on another piece.

## Conclusions…

As you can see, all of these calculations are relative, and they do not provide absolute answers, but guidelines. You can not type a few values into the spreadsheet and get out a best in slot, time to live gear set. This is a very specific tool for a very specific, however powerful, purpose. It will allow you to compare gear sets for given fights to tell you which one will provide you with the most effective health for surviving burst. It will also provide you with modified Stamina to Armor ratios for any fight, accounting for all magical and bleed damage that you take. And it will give you everything you need to understand if you can survive the burst that just killed you by swapping gear.

However, it is important to understand that if you provide the wrong data for the specific required fields, it will provide you with the wrong answer. You must evaluate where you want to obtain X and Y from the parses, and use the correct situations and values in order to get the proper outcome. If you do this, it will help you immensely in your struggles to get past tank checks, but only if you select the correct values.

My one concern with the data that’ll be going into this sheet is I’m assuming you’re pulling the percents of magical/bleed damage from a log parse, right? If, for example, WoL says on a fight you took 50% physical damage, 25% magical, and 25% bleed, wouldn’t the 50% physical damage number be post-armor (already mitigated) therefore under-representing how much physical damage there is (or over-, depending on how much armor you have)? And in turn wouldn’t this over-represent how much magical/bleed damage there is?

I’m guessing it’s safe to assume that a community-generated average of magical/bleed damage from fight to fight will probably be necessary, rather than using what one sees in their own parses?

Er, not to sound like I’m down on the spreadsheet. I think this has to potential to fine-tune to a precise science gear sets from fight-to-fight. Great job thus far!

A few things.

First, with respect to the post armor damage intake, Theck addressed this with in the topic on Maintankadin. His formula accounts for the damage number we are plugging in to be already modified by the armor and resistances that you have, that is why there is a field for you to input those values. The Calculation is made for post damage reduction.

Second, you are correct to assume that community generated averages will be necessary to get a general value for X and Y specific to fights, however you can use the spreadsheet as a personal tool on the spot when you die, to see if more armor, or more stamina, or more resistance will increase your effective health and allow you to survive the burst.

I have not expanded the tool to show all fights, those data sets have to be plugged in manually. Later revisions may include typical values of X and Y generated by the community and a selection tool by boss.

Your value for magical mitigation is mistaken, unless I’m missing something. It should be 0.94 (Imp Rf) x 0.97 (Shield of the Templar) x 0.97 (BoSanc) x 0.97 (Glyph of Divine Plea) x 0.94 (Guarded By the Light), 19.35% in total. Unless I’m way off base. I think the value for physical mitigation might be high, too, I seem to remember 14.5% as the final number.

Yeah i just stole those numbers from theck, on a post on page 2 I believe. I will have to go back and verify them

Wrathy,

Do you plan on making this available or did I just miss the link? I poked around (albeit not too diligently) on Maintankadin and didn’t see anything.

I am still testing it, and based on the discussion that is going on over at maintankadin, there are still some bugs that have to be worked out. I hope to have it up by the time ICC comes out, aka next week.