DPS and EHP Spreadsheet

  • #1
    This is the DPS and EHPspreadsheet I've been working on. It was made with a wizard in mind but so far should work for any class that is not dual wielding, though it currently does not support any class specific buffs or passives besides some of the wizard ones. If you're having any issues with the spreadsheet please read the "Notes" tab to make sure you're entering the correct information. WEAPON +DAMAGE% now has a box in the spreadsheet, so dps values should be accurate.
    In case there's any confusion, your Int, Vit, Str,DPS, HP, CC, CD, APS should exactly match the in game value (APS rounds to 2 decimals though). Your armor and lowest resist should match the in game values exactly, though they might differ by 1 due to rounding on the char sheet.

    Detailed version with full gear stats comparison
    https://drive.google.com/file/d/0B4-opYZrg_XRcE9RR0tVRTBwUlk/edit?usp=sharing

    Features:
    The spreadsheet features the ability to input your total stats and weapon stats to compute your dps and EHP. There's also a separate tab where you can compare different gear sets. The DPS value should match your in game Damage Number, provided all applicable stats are entered correctly. Additionally the spreadsheet computes relative dps gains for a range of dps stats and also reports those values in terms of comperable values of your primary stat, i.e., how many Intellect is 1%IAS worth, etc.

    Here are some more specific features:

    1) Calculates unbuffed dps, char sheet buffed dps, and effective char sheet dps. Char sheet buffed dps is what you will see in game based on all your skills. The effective char sheet dps is what your buffed char sheet dps would be if it included all the bonuses for the skill you use (such as +disintegrate%) and elemental damage type (like +arcane damage%).

    2) Paragon point table where you can input how many points you spent on each stat. The table also shows you the relative gain of a paragon point for each stat to your effective char sheet dps and your EHP.

    3) A second sheet allows you to compare two gear sets and displays the difference in EHP and DPS.

    4) Relative dps and EHP gains for each stat are shown (int, vit, crit%, etc.).

    5) A comparison of Marquise Ruby vs Emerald is shown for easy reference, or the gear comparison can be used to compare the different gems at higher levels. For most people Emerald will be much better now that weapon damage% has been drastically cut on new drops (used to go up to 50%, now it's like 10%, if that).


    Limitations and Workarounds
    Does not compute toughness stat, and toughness is not the same as EHP. I have no plans to implement functionality to compute toughness.

    The spreadsheet does not work well with weapons that have a base attack speed of 1.15 APS, or any other base APS not divisible by 0.1. The workaround currently is to set the 1.15 APS weapon to 1.15 APS in the spreadsheet, and put 15% IAS on the weapon IAS cell in column C. That tricks the spreadsheet into computing the correct IAS. If your weapon does have IAS, I'm still working on a solution.

    If you have any issues downloading or using the spreadsheet, feel free to post here or send me a PM after you've read the Help tab.


    Currently planned additions: update to include other buffs like cold blooded and audacity/
    EDIT:
    3-15-14 Updated based on 2.0 changes
    3-21-14 Updated formulas to account for AR from sockets
    3-25-14 Updated Crit Damage to display the amount including paragon levels.
    3-28-14




    • Updated Mob level to 73 for Armor and resist mitigation and EHP calcs
    • Updated Str, vit, and Int for a lvl 70 character
    • Added Unwavering Will to buffs list
    • Updated Enchantress EHP calculation to include armor from paragon points
    • Updated HP calculation based on lvl 70 character
    3-29-14 Corrected EHP calculation with Unwavering Will
    3-30-14 Corrected Paragon armor contribution on Full Gear Comparison tab
    Added percent differences for DPS and EHP values between Equipped Items tab and Full Gear Comparison tab
    3-31-14 Updated Life per Second from paragon levels to the correct amount for level 70 (165.1)
    4-13-14 Updated Elite damage bonus calculation to be consistent with changes in game. It now is just an additve modifier.
    Changed fill for cells C12 and C13 since they should remain blank
    5-9-14 Corrected the Life% display on Equipped Items and Full Gear Comparison tabs to correctly include the amount from paragon points
    5-22-14 Updated APS calculation to account for change in Enchantress contribution. Recent patch changed it from +0.03 APS to +3% IAS.
    5-30-14 Fix a bug with Primary relative dps gain calculation on 'Equipped Items' tab that would give incorrect values when the enchantress was indicated as being used
    5-31-14 fixed weapon damage% affix on weapons to work correctly with elemental damage, per patch 2.0.5 changes
    Last edited by Loroese: 5/31/2014 9:11:24 AM
    Wizard DPS and EHP Spreadsheet, mostly useful for wizards.
    Crusader DPS and EHP Spreadsheet, meant for Crusaders
    My Wizard
  • #2
    Interesting. There are so many times I wodner if I get a for example 900dps weapon with +100 int boost is it gonna be better than my 1000dps weapon with no boosts, but I just cant buy the weapon just to see if Ill get a damage boost(or if its negligible) so later I find out it wasnt worth it u know...
  • #3
    Great, thanks for sharing. I will try changing a few pieces around and see if I can see any incosistence :)
  • #4
    awesome stuff can we get this stickied?
    Quote from shaggy

    Thumbs up for them trying to spread arcane sentries out a bit more.

    Arcane + Horde was like trying to play laser tag against 5000 midgets hopped up on caffiene and diet pills.
  • #5
    Nice one,

    I am considering building an application that ghaters all the data posted in the forums, incase i build it, do you have any problem with me using your research? You will have the proper credits.
    "When the government violates the people's rights, insurrection is, for the people and for each portion of the people, the most sacred of the rights and the most indispensable of duties"
    - Gilbert du Motier, marquis de Lafayette
  • #6
    This thing is awesome. Finally I can go buy something expensive in the AH because I won't be guessing.
  • #7
    Quote from compLex1ty

    Nice one,

    I am considering building an application that ghaters all the data posted in the forums, incase i build it, do you have any problem with me using your research? You will have the proper credits.


    Nope, I have no problem with that or with anyone editing or modifying my spreadsheet or equations in any way.
    Wizard DPS and EHP Spreadsheet, mostly useful for wizards.
    Crusader DPS and EHP Spreadsheet, meant for Crusaders
    My Wizard
  • #8
    Im sorry my ignorance but how could one use that knowledge to know if for example a weapon for sale will be better than my own?
  • #9
    I just recently switched from a slow 2h to a fast 1h and noticed a few interesting trends in dps when comparing different weapons. The abreviated version is that when using an OH I get a higher damage number from a slightly lower dps (905.1dps) weapon that had +attack speed than a weapon without (939.9 dps).

    For a 2h the main stat that mattered when it came to dps was the weapon dps. Attack speed weapons gave the same dps as just +xx% dmg weapons. When looking at 1h I noticed that I could get more dps from a 1h with IAS than a slightly higher dps weapon with just+50% dmg or so. Of course, when comparing my 2h to my 1h I included my OH, which is a source with a lot of +dmg. I haven't read a lot of theorycrafting ideas relating to this game yet but I think the reason for the differences can be explained by thinking of attacking as using both your MH and OH simultaneously (I know that's not really how the game works, but when not dual weilding, mathematically you combine the MH and OH stats every attack). Your OH 'attacks' just as frequently as your MH so that if you increase the frequency of MH attacks, you increase frequency of OH 'attacks'. Your OH stats are also completely independant of your MH stats so if you get a faster weapon, you get more effective dps out of your OH. Since IAS on a weapon makes the attack speed faster, it leads to more dps than you would get from an equivalent dps weapon with no IAS.

    Quick comparison based on my current spreadsheet:

    Current Weapon
    364-816 dmg, 905.1 dps, 18% IAS, 1.3 attacks per sec base (back calculated)
    effective attacks per sec = 1.3*1.18 = 1.534 but only shows 1.53 on the tooltip

    Alternative Weapon
    459-987 dmg, 939.9 dps, 1.3 attacks per sec, no relavent stats just +dmg% and bonus dmg

    OH
    really any OH would work, but here's mine because that's where my numbers come from
    +103-360 dmg, 82 Int

    When I put those into the comparison area of my spreadsheet it tells me I lose 499.3 dps with the HIGHER dps weapon. On a side note, if my OH was only +103-190 dmg, I would gain 14.4 dps from switching weapons.

    So to summarize: If using any gear with +dmg, be it rings, necks, or OHs, you might be able to gain dps by switching to or choosing a lower dps weapon with +IAS compared to a weapon without +IAS.

    If anyone questions the calculations of the spreadsheet I'd recommend buying a couple really cheap weapons on the AH, get 2 with about the same dps, one with IAS one without and just equip them and check your in game damage number. As long as you have any +dmg gear not including your weapon, I would expect the IAS weapon to be higher dps than the other, all other things being equal. Unfortunatelly, this means that when shopping for a new weapon you might have to compare several different weapons and look at a lot of different stats to find the one that actually improves your damage number the most for your price range.
    Wizard DPS and EHP Spreadsheet, mostly useful for wizards.
    Crusader DPS and EHP Spreadsheet, meant for Crusaders
    My Wizard
  • #10
    Quote from Madwizz

    Im sorry my ignorance but how could one use that knowledge to know if for example a weapon for sale will be better than my own?


    If you open the spreadsheet you will notice a few red boxes. Fill in your in game stats in there including weapon damage, both low and and high end, weapon speed shown in the tooltip, your total crit% and crit damage % shown in the details area of your character sheet/inventory, as well as the total of all +attack speed on your gear besides your weapon, and any +dmg stats you might have. For example, if you have a +2-4 bonus dmg ring, put 2 in the min+dmg and 4 in the max+dmg box. If you have multiple cases of +dmg, add them up. I'll probably edit the spreadsheet later to have multiple boxes for those values so you don't have to add them manually but for now just put the totals. Once again, do not include any of the stats on your weapon in the boxes aside from the base weapon damages and attack speed. If everything is entered correctly the DPS box should show the damage number indicated on your character screen, and the Mod AS box should show your total number of attacks per second shown on your char details screen.


    To compare weapons, put the stats of your currently equipped weapon in the Equipped box, including any Int it might have on it, and the attacks per second shown on the weapon in the attack speed box, but do not put any of your weapon stats in the +dmg boxes, even if your weapon has say +100-200 bonus dmg. That value is already included on the total weapon damage displayed. Change the last box (Weapon?) from 0 to 1, or just don't include any values for +attack speed on the weapon. In the next column, input the stats of any weapon you want to compare, including the weapon attack speed. Again, ignore any +bonus damage or +% damage, and you can also ignore any +attack speed. If you are using an offhand, include any relavent stats with the 1h. Using a source means putting the +xxx-yyy damage into the Min +dmg (xxx) and max + dmg (yyy) boxes, and if you have any overlapping stats, like int on both MH and OH, just add them together and input the total.

    In the DPS difference box, you should see a number under the Item 1 column. That number represents how your dps will change if you equip the new item instead of the old. If it is negative, that means you lose dps from switching. The same goes for the Item 2 column and in both cases compares the new dps to your current dps, only switching the one item.

    I'd recommend trying it out with a couple random rares you have laying around in your bags or can find off mobs to make sure the spreadsheet is giving the correct results. If you have any other issues or questions, just ask or send a pm, but I would definitely make sure the spreadsheet is working correctly before making any significant purchases.
    Wizard DPS and EHP Spreadsheet, mostly useful for wizards.
    Crusader DPS and EHP Spreadsheet, meant for Crusaders
    My Wizard
  • #11
    nice, do I need a gmail or google chrome to edit that spreadsheet? it seems I cant edit anything(Im using firefox).
  • #12
    You should be able to just save a copy then edit it all you want.
    Wizard DPS and EHP Spreadsheet, mostly useful for wizards.
    Crusader DPS and EHP Spreadsheet, meant for Crusaders
    My Wizard
  • #13
    After seeing the other post regarding EHP in Inferno I decided to make my own EHP calculator in my spreadsheet. I used the damage resistance formulas from the site listed below but had to figure out the armor calc for FA+glass cannon manually. Like the webpage with the EHP calculator you should be able to just input your stats from your details page and then indicate if you have Force Armor and/or glass cannon active and it should be able to calculate your EHP from there. I also added the conversion of Str and Int to armor and resist respectively so it should completely calculate your new EHP for the new item.

    http://mmo-mechanics.com/swtor/forums/Thread-Diablo-3-formula-list

    If there are any errors or mistakes, please let me know and I'll fix them as soon as I can. Also, if anyone downloaded a previous version before reading this post, I recommend you re-download to make sure your version doesn't have any errors the previous version might have had (there were a few, and I updated the EHP part just before posting this).
    Wizard DPS and EHP Spreadsheet, mostly useful for wizards.
    Crusader DPS and EHP Spreadsheet, meant for Crusaders
    My Wizard
  • #14
    Ok I downloaded it, but Im having some trouble filling in the stats: Weapon Attacks per sec: in this one I put the base weapon speed which is 1,2 in my case Weapon +attack speed: in this one I also put 1,2 since my weapon has no inherent speed bonus, correct? Primary Stat: In this one I filled with the weapon DPS Min Weapon Damage: this one was min from weapon damage shown above attack speed of weapon Max Weapon Damage: same as above Min + Dmg: from OH and an ammy Max + Dmg: same as above Crit %: ok Crit Damage %: ok Attack Speed: put the same as the other above which is the weapon base speed(1,2) Bonus Damage %: I put 15% from glass cannon correct? +Attack Speed: in this one I put bonuses from rings, wrists, gloves= 58%, if the weapon had any shouldve I get it included? Weapon: 1 for yes and 0 for no? dont get this one sorry.

    My DPS shown on the sheet is 27K but my DPS shown on the char screen is 37K, so I think I screwed up somewhere, Im not using force weapon btw, only glass cannon.

    sorry about the color on the things lol
  • #15
    Hi Madwizz.

    I believe I can be of some assistance in our struggle to come to terms with the workings of this sheet (I actually solved my remaining unclarity while typing this).

    Fill in these boxes (red font on yellow background) - Tells you which boxes to fill in and which boxes to leave alone. Only fill in boxes with red font. Most of the others will have formulas and thus be automated in the determination of their contents.

    • Weapon Attacks per sec - Weapon Attacks per Second as read from your weapon tooltip.
    • Weapon +attack speed - I guess that it is "Increases Attack Speed by xx%" as read from your weapon tooltip, but this would not make immediate sense since all other inputs that come in % are labeled accordingly on the spreadsheet, and since this variable is already included in the original "Attacks per Second" display on the weapon which we entered right before. Despite of that I get the closest match for [Mod AS] if I put this increased AS% from my weapon here.
    • Primary stat - Your class's primary attribute, the one which yields increased damage for that class (int for wizards & wds, dex for monks & dhs, str for barbs).
    • Min Weapon Damage - Minimum weapon damage as displayed in white on your weapon tooltip.
    • Max Weapon Damage - Maximum weapon damage as displayed in white on your weapon tooltip.
    • Min + Dmg (total or each item in each box) - Bonus minimal damage from armor, including off-hand. Enter the sum of it in the white box or use the yellow boxes for each part, leading to the white box to calculate and display the sum for you.
    • Max + Dmg (total or each item in each box) - Same as above.
    • Crit % (on char sheet) - Obvious.
    • Crit Damage % (on char sheet) - Obvious.
    • Actual APS (displayed on weapon) - This is a white box, indicating that you should not input anything here.
    The formula used is round([Weapon Attacks per sec]/(1+[Weapon +attack speed]);1)*(1+([Weapon +attack speed]/100). Round(content;1) is a function which will round that content to no more than one decimal places. I can tell you that Weapon +attack speed is divided by 100 because the number we use for it is in percent, and that's simply the way it is converted in order to be processed in a calculation. 17% (per cent) = 17/100 = 0.17. All that aside, I have no idea why that formula is there and why it is necessary.
    • Bonus Damage % (on char sheet) - This is most likely "Damage Increased by Skills" as read from your character sheet.
    • +Attack Speed % (not including weapon) - Obvious as well, this field is for attack speed bonuses from armor. The white box will yield the sum of it, while the yellow boxes can be used to enter each bonus individually.
    • Mod AS (should be same as on char sheet) - Again a white box. This should output your actual amount of attacks per second as can be read from your character sheet, though your character sheet will round it to two decimal places. White boxes such as this can be used to check back with your character sheet.

    Then we're already at the white box which should tell us our supposed DPS, matching the one on our character sheet.

    I got my DPS to match thanks to your question. Maybe yours will work out with this as well. Yay us!
  • #16
    Ok thank you, I got the primary stat wrong(I put the weapon dps instead of intel lol) now its working. Only thing that is a bit off is my attack speed. For some reason I have 1.9 attacks per second, but in the spreadsheet it appears as 1,918, so in the spreadsheet my damage is boost a little bit(like more 500 damage out of 37k) so I guess its fine, cheers for the help this thing is awesome.
  • #17
    Some spells and passives could greatly affect your DPS.. It would be nice if you could add more spells other than just glass cannon, but that would make things much more complicated.
  • #18
    outofhell has the breakdown pretty spot on. The reason I included a box for weapon +attack speed, despite it already being included in the weapon dps and not really used for dps calcs, is because I noticed the actual weapon speed is only displayed to 2 decimal places in game but it seems the game uses more than just 2 decimals. To use my weapon as the example, it has 1.53 APS on the tooltip with 18% +AS. The weapon type has a base speed of 1.3 APS and if we increase the speed by 18% the actual APS is calculated to be 1.534. If I use 1.534 for the actual APS the spreadsheet calculates my damage number precisely to 2 decimals. If I just use 1.53 I end up being off by a little. If your weapon has no IAS, just leave it at 0 and the "Actual APS" and "Weapon Attacks per sec" boxes should have identical numbers.

    I suppose I could add an extra page to the spreadsheet with better details about what each box means and what to input.

    Quote from Madwizz

    Ok thank you, I got the primary stat wrong(I put the weapon dps instead of intel lol) now its working. Only thing that is a bit off is my attack speed. For some reason I have 1.9 attacks per second, but in the spreadsheet it appears as 1,918, so in the spreadsheet my damage is boost a little bit(like more 500 damage out of 37k) so I guess its fine, cheers for the help this thing is awesome.

    Can you post or pm me your stats including weapon speed? It sounds like there's still something slightly off if your APS is showing 1.918 and your weapon is 1.9 APS. That's the "Actual APS" box right, and not the "Mod APS"?

    I have found issues with weapons having +dmg% though, so if your weapon has that in addition to +IAS that could explain the difference. I don't really understand what the issue with +dmg% is, and I spent a good couple hours crunching numbers with a couple different weapons but couldn't find the right calculation method to end up with weapon stats yielding the correct damage number. If anyone has found or knows what formula they use to calculate damage number using weapons with +dmg I would love to see it, my best guess so far is they take the base weapon stats and multiply them by (1+xx/100), then round the values off to the nearest integer on the tooltip display. I was able to use that formula to get my 2h with +50% dmg damage number to match my spreadsheet by back calculating the base weapon stats, much like how I back calculate the weapon AS, but using a fast dagger with +16% AS I couldn't get the numbers to work out.

    Quote from purpleswag

    Some spells and passives could greatly affect your DPS.. It would be nice if you could add more spells other than just glass cannon, but that would make things much more complicated.


    Those spells and passives should be shown on the details part of your char sheet in "Damage Increased by Skills". If you put that in the "Bonus Damage %" box, it should match up with the number shown in game. The only expection is Magic Weapon, which is said to produce an incorrect value for your damage number in game, so when using the spreadsheet I recommend not having Magic Weapon active. If you remove and re-equip a weapon it will remove the buff, so do that if you have it active but want to use the spreadsheet. Otherwise the in game damage number should match the DPS of the spreadsheet to 2 decimals, though there still are some issues with +dmg% weapons that might cause you to have to fudge your weapon damage numbers slightly to get an exact match. As far as I can recall only glass cannon and Familiar with sparkflint change that value and the spreadsheet should work with either, both, or neither as long as the correct Damage % is put into the spreadsheet. If you find that is not the case, please let me know and I'll get it fixed.
    Wizard DPS and EHP Spreadsheet, mostly useful for wizards.
    Crusader DPS and EHP Spreadsheet, meant for Crusaders
    My Wizard
  • #19
    I dont know what I was doing wrong before, now its quite accurate, and teh 1.91.. thing was in the MOD AS like you said but the DPS is the same, so I guess its working well, its interesting this because now I know I dont want to get enough gold to buy a 10 mil rare weapon to only get an 10% DPS increase over my vanilla 1 hander... thanks, this thing really is awesome, hats off to you sir.
  • #20
    Ive recently found 2 rare weapons with some sweet mods, one is a 714 dps 1 hander ok when I put it on my dps decreases by like 800damage, and the other is a sweet little dagger with 648 DPS, although when I put it on it does about the same damage as the 714 dps one hander, and both do about 800damage less then my 966DPS vanilla mace. Granted both of them add like 150 intelligence and the dagger gives 51%+ critical damage, but its still funny to see they all are about the same level(damage wise). In the spreadsheet though the numbers are a little off since it shows those 2 new weapons as "worse" then they appear in my charscreen damage... both of those weapons have %ias, maybe its something to do with that.
  • To post a comment, please or register a new account.
Posts Quoted:
Reply
Clear All Quotes