A DPS Spreadsheet Calculator(WIP)

[swapoer]

I have finish White March II and reach level 16 and the only thing left for me to complete is the Act III.I have dumped 100 hours into this game and I am currently taking a break, so I think maybe I should give something back to the community.

Here is the download link for the DPS Spreadsheet Calculator:

https://drive.google.com/file/d/0B_gwdYqK9oOlMjdmS2JaUDhOdkU/view?usp=sharing

 

1 Important Notes

1.1 The attack time of this spreadsheet is based on: 

https://forums.obsidian.net/topic/86684-mechanics-the-big-attack-speed-conundrum/?p=1808217

I take 0.2 second for idle time in calculating the full attack cycle time.

 

1.2 The spreadsheet is inspired by the below link.

https://docs.google.com/spreadsheets/d/1vDm5MOrCK6S95h5u0EzZbGTv_u_rYqjuO0-zupqk0_A/edit?pli=1#gid=1264774515

I found it difficult to use and the attack time is not as accurate as the link above, so I decided to make one for my personal use.

 

1.3 The spreadsheet is developed for my personal use and should be considered Work In Progress.

 

1.4 What this Spreadsheet can do

It can calculate your 2h/1h/dw weapon’s DPS and I include all buff/talent/abilities/potion/drug that will affect your attack speed. Please refer to section 1.1, if you want to know more about how the speed bonus works.

Calculating the DPS when Dual wielding different weapon will need some little work on your behalf, which will be explained in section 4.2.

 

1.5 What this Spreadsheet can’t do

I am yet to include Monk’s Fist into the spreadsheet.

Nature's bounty, spell tongue, their champion braved the horde and spell tongue’s attack speed effect is included in this spreadsheet. Because I am unable to find these buff’s other effect, I don’t include the other effect in the spreadsheet.

Wounding, a weapon enchantment, is not included in this spreadsheet, due to I don’t know how exactly it will affect the damage.

 

2 How to use the DPS Calculator

First and foremost, all the below data are found in the "DPS Calculator" sheet.

Hovering your mouse over the title of each column, that is the cells in the second line of the sheet, will show you some notes for what this column stands for.

 

The only columns require your input is Weapon Type column©, Weapon Enchantment column(H-Q), Talent column(R-AD), Equipment column (AE-AJ), the Character column (AK-AM), Other (AN-AS) and Speed Buff (AT-BH). Then you will get your DPS in DPS Result Column (D-G).

In Column D, you can see how many seconds it takes for you to make an attack.

In Column E, you can see your DPS against a DR 10 enemy.

In Column F, you can see your DPS against a DR 15 enemy.

In Column G, you can see your DPS against a DR 20 enemy.

 

I think an example would the best way to show how to use this spreadsheet.

For example, I am in possession of an Exceptional Estoc and The hours of St.Rumbalt and I want to know which weapon would give me the best DPS output and which talent, Savage Attack or Vulnerable Attack is the best choice for my next talent choice.

 

2.1 In the Weapon Name column(B)  and Weapon Type column©

I put in the name of the weapon and choose the right weapon type for the weapon. After choosing the weapon type, the Weapon Basic Data Column(BI-BR) will generate the basic weapon data for the corresponding weapon type.

After doing this, the third line is for Exceptional Estoc and the fourth line is for The hours of St.Rumbalt.

 

2.2 In the Weapon Enchantment Column(H-Q)

General Knowledge:

In the Quality Column(H): 1 means fine; 2 means exceptional;3 means superb;4 means legendary.

The Dmg and Acc column(I/J) should be easy to understand: 1 for level I; 2 for level II; 3 for level III.

In other Columns: 1 means Yes; 0 or empty means No.

 

For Exceptional Estoc (the third line):

Since Exceptional Estoc is an exceptional weapon, I put 2 in the Quality column(H).

Because I have the resource to give Burning Lash enchantment, I put 1 in Lash column(K).

 

For The hours of St.Rumbalt (the fourth line):

Sine The hours of St.Rumbalt has Accurate 3 enchantment, I put 3 in the Acc column(J).

Sine The hours of St.Rumbalt has Annihilation enchantment, I put 1 in the Anni column(O).

Because I have the resource to give Burning Lash enchantment, I put 1 in Lash column(K).

 

2.3 In the Talent column(R-AD),

Since I have two-handed style talent, I put 1 in the 2h column(AB).

 

Since I also would like to know which talent, Savage Attack or Vulnerable Attack, will give me the best DPS output, I copy the third and forth lines and paste it twice below.

In the Notes column(A) of the fifth and the sixth lines, I type SA, which means Savage Attack and I put 1 in Savage Attack column(X) of the fifth and the sixth lines.

In the Notes column(A) of the seventh and eighth lines, I type VA, which means Vulnerable Attack and I put 1 in Vulnerable Attack column(W) of the seventh and eighth lines.

 

PS:The Notes column(A) is just for you to type in any notes for easy to know how this line is different from the other lines.

 

2.4 In the Equipment Column (AE-AJ)

Since I am wearing a -15% penalty armor, I put -15% in Armor column(AH).

Since I am wearing the Gauntlet of Puissant Melee, I put 1 in Gauntlet Melee column(AG).

 

PS: Gauntlet of Puissant Melee gives 10% of melee damage.

 

2.5 In the Character Column (AK-AM)

I will put in the DEX and MIG of my character here, which are both 20. In the Acc Column(AM), I give a number of 15, which means my character has 15 more accuracy than the deflection of enemy.

 

PS: The truth is you could put any number in the Acc Column(AM), and I will explain why 15 is a good default accuracy advantage number later.

 

2.6 The DPS Result Column (D-G)

In Column D, you can see how many seconds it takes for you to make an attack.

In Column E, you can see your DPS against a DR 10 enemy.

In Column F, you can see your DPS against a DR 15 enemy.

In Column G, you can see your DPS against a DR 20 enemy.

 

After following through process 2.1 to 2.6, I can conclude that Exceptional Estoc give me better DPS output than The hours of St.Rumbalt and Savage Attack give me better DPS output than Vulnerable Attack. So I choose Exceptional Estoc for my main weapon and choose Savage Attack as my next talent.

 

PS: I don't take the Overbearing enchantment into account, since it has no effect on DPS.

 

2.7 Further Explanation

The Speed Buff column (AT-BH) is any buff which will change your attack speed. Since some of them are not additive, including all of them in the spreadsheet maybe is an easy way to use.

The Others Column(AN-AS),is for further modification of the data which will affect DPS. I don’t include all the talent/equipment/buff in this spreadsheet, but you can use the Others Column(AN-AS) to add any effect of talent/equipment/buff, like what I did in section 3.5 for bless and dire bless effect. As far as I know most of the bonus in Others Column(AN-AS) is additive, so you can just type in the exact number of bonus in the corresponding cells.

The Final Column (BS-BY),is the final data that will go in to the calculation of your damage. These data derive from data of Weapon Enchantment, Talent, Equipment, Character, Weapon Basic Data, Speed Buff and Others columns. 

The Calculation Column(BZ-CX) will calculate your damage in Graze, Hit and Critical Hit catalogues, basing on the data from the Final Column (AF-AN).

 

2.8 Sum it up

Because most of the functions are stored in cells, when you try to add a new weapon, you just need to copy an existing line and paste it into an empty line. After that, you can edit the line to change it according you desire.

The only columns require your input is Weapon Type column©, Weapon Enchantment column(H-Q), Talent column(R-AD), Equipment column (AE-AJ), the Character column (AK-AM), Other (AN-AS) and Speed Buff (AT-BH). Then you will get your DPS in DPS Result Column (D-G).

 

3 The Damage part of DPS

 

3.1 TLDR: The short Version

 

When 16 < attacker's accuracy - defender's defense < 50, the following rule hold:

Rule 1: 1 additional point of accuracy increase will give you 1% of your weapon's basic damage on average.

PS1: When you have the Elemental Lash enchantment on your weapon, 1 additional point of accuracy increase will give you 1.25% of your weapon's basic damage on average.

PS2: Critical Hit Damage Modification, that is, damage bonus which only takes effect on a critical hit, will change this number. 50% critical modification will change the default 1% into 1.5%.

PS3: The total effect of PS1 and PS2 will change the default 1% into: (1% + Critical Hit Damage Modification)*1.25

 

When 16 < attacker's accuracy - defender's defense, the following rule hold:

Rule 2: additional 1% of damage bonus increase will give you 1% of your weapon’s basic damage on average. (This rule don’t hold when: attacker's accuracy - defender's defense < 16)

PS1: When you have the Elemental Lash enchantment on your weapon, 1 additional 1% of damage bonus increase will give you 1.25% of your weapon's basic damage on average.

PS2: Critical Hit Modification has no effect on this rule.

 

Since I am calculating DPS, I don't bother about the "when attacker's accuracy - defender's defense < 16" situation.

Most of the time, your DPS dealer's accuracy should fall in the "16 < attacker's accuracy - defender's defense < 50" range, or you are not a qualified DPS dealer.

 

With the above principle, you will have a easy time when deciding between a Gauntlet of Puissant Melee (+10% melee damage bonus) and a Gauntlet of Accuracy (+5 acc). After considering the chance of miss, graze, hit and critical hit, assuming you fall in the "16 < attacker's accuracy - defender's defense < 50" range, the Gauntlet of Accuracy will give you 5% increase in melee damage (this 5% is based on your weapon's basic damage) or 6.25% increase if you have the Elemental Lash Enchantment on your weapon. The choice should be quite apparent.

 

The above rule hold, even when taking DR into consideration, as long as your graze damage don’t do the minimum 20% of your graze damage, which happens when 80% of your graze damage is lesser than the defender’s DR, in which case you will do the minimum 20% damage regardless how high the DR is..

 

3.2 Accuracy

In section 2.5, I talk about I will explain why I use 15 as a default accuracy advantage number.

For basic how Attack Resolution work, please refer to this link:

http://pillarsofeternity.gamepedia.com/Attack_Resolution

I assume you have read the above link or you know how combat resolution work in this game.

 

In the "D&CTH@＞" sheet, frome cell D1 to CZ1, I list the accuracy advantage against the enemy. The second, third, fourth and fifth line are just the corresponding chance of Critical Hit, Hit, Graze and Miss, following the above link's mechanic explanation.

 

3.3 Damage Bonus

As for damage bonus, most of the damage bonus work in an addictive way and it is based on your weapon's basic damage. For example, you have a Might 20 (+30%) and two-handed weapon style talent (+15%), when you critical hit (+50%), you will do 195% (100%+30%+15%+50%) of your weapon's basic damage.

Elemental Lash is an exception, it deal 25% of your total damage before armor reduction and when calculating the final damage, Elemental Lash only take 25% of the enemy’s DR.

 

We will follow the above example and assume your weapon deal 10 basic damage of pierce and enemy have 4 DR against pierce and fire and you have burn lash enchantment. When critical hit, you deal 195%*10=19.5 pierce damage and additional 19.5*25%=6.25 fire damage. After taking DR into account, you will deal 19.5-4=15.5 pierce damage and 6.25-4*25%=5.25 fire damage.

 

One thing to keep in mind is that most of the damage bonus is based on the basic damage of your weapon. Elemental Lash and Wounding are exception.

 

Every time you right click on a weapon, the damage section which the pop up window show you is not the basic damage of the weapon but rather the result of basic weapon damage, weapon enchantment’s bonus and your might’s bonus. When you move your mouse over the damage number in the pop up window, it will show you a lesser number of damage in tooltip, which is still not the weapon's basic damage but rather the result of basic damage and weapon's enchantment.

To get the basic damage number of weapon, you have to equip it in the inventory panel and hover your mouse over the damage number under your portrait. In this way, the game will tell you what the basic damage of the weapon is and how many damage bonus you have.

 

3.4 Put Accuracy and Damage Bonus together

 

When your accuracy is equal to the defense of enemy, you have 15% chance of miss,35% chance of graze,50% chance of hit, and 0% chance of critical hit.

Every point of accuracy increase starting from here will give you 1% chance of critical hit and -1% chance of miss.

 

When "attacker's accuracy - defender's defense =15", you have 0% chance of miss, 35% chance of graze,50% chance of hit, and 15% chance of critical hit.

Every point of accuracy increase starting from here will give you 1% chance of critical hit and -1% chance of graze. If the default Critical Modification is still +50% damage, then every point of accuracy increase will give you 1%*(150% + Damage Bonus)-1% *(50% + Damage Bonus)=1% of your weapon's basic damage, regardless the exact number of damage bonus of your character.

If you have Elemental Lash enchantment on your weapon, then the number will be changed into 1.25%.

If you have any critical modification which increase the damage bonus when critical hit, then the number will change accordingly.

For example, If you are using Battle Axe, which give a 50% damage bonus when critical hit, then the number will be 1%*(150% + Damage Bonus + 50%)-1% *(50% + Damage Bonus)=1.5%.

Since all the firearms come with a inherent -30% Critical Hit Modification, 1 point of accuracy increase will give them only 0.7% damage increase.

 

To sum it up, for accuracy: When 16 < attacker's accuracy - defender's defense < 50, the following rule hold:

Rule 1: 1 additional point of accuracy increase will give you 1% of your weapon's basic damage on average.

PS1: When you have the Elemental Lash enchantment on your weapon, 1 additional point of accuracy increase will give you 1.25% of your weapon's basic damage on average.

PS2: Critical Modification, that is, damage bonus which only takes effect on a critical hit, will change this number. 50% critical modification will change the default 1% into 1.5%.

 

Since when "attacker's accuracy - defender's defense =15", you have 0% chance of miss, the damage bonus will always take effect no matter what the attack roll is. Because of this, we get the rule II:

When 16 < attacker's accuracy - defender's defense, the following rule hold:

Rule 2: additional 1% of damage bonus increase will give you 1% of your weapon’s basic damage on average. (This rule don’t hold when: attacker's accuracy - defender's defense < 16, because of the existence of miss)

PS1: When you have the Elemental Lash enchantment on your weapon, 1 additional 1% of damage bonus increase will give you 1.25% of your weapon's basic damage on average.

PS2: Critical Hit Modification has no effect on this rule.

 

This the reason why I use 15 as a default number of accuracy advantage against enemy in section 2.5. It is a reasonable requirement for you DPS dealer and it will make your assessment of accuracy bonus and damage bonus easier and linear.

 

3.5 Bless or Dire Bless (Hit to Critical Hit Covert)

 

Bless give you 5 accuracy bonuses and 10% damage bonus. With the analysis in 3.3, we can easily say Bless will give us 15% damage bonus in result, if you don’t have elemental lash and additional Critical Hit Damage Bonus. If you have elemental lash then it is 18.75% damage bonus.

 

Dire Bless give 20% Hit to Critical Hit Covert Chance, which means when it is a hit there is 20% chance it will be changed into a critical hit. Since in the range of "16 < attacker's accuracy - defender's defence < 50", you will have 50% chance of hit and I do believe most of the time you will fall into this range, Dire Bless give you 20%*50%=10% more chance of critical hit. At the same time, when you get that 10% additional chance of critical hit you are losing 10% chance of that normal hit, so the net result is 20%*50%*[(150% + Normal Damage Bonus) - (100% + Normal Damage Bonus)]=5%. In a nutshell, you only get 5% of you basic weapon damage out of Dire Bless on average, if you don’t have elemental lash and additional Critical Hit Damage Bonus.

 

For a level III spell, Dire Bless quite lack lust.

 

You can easily test the above conclusion using my spreadsheet.

For example, Copy the third line and past it into the empty ninth and tenth line.

In cell 9A, put in Bless for easy reference. Put 10% in cell 9BU for additional 10% damage bonus from bless. Put 5 in cell 9BV for additional 5 accuracy bonus from bless.

In cell 10A, put in Dire Bless. Put 20% in cell BY10 for the 20% Hit to Critical Hit bonus from Dire Bless.

Then, you will see Bless give you better result in DPS than dire bless.

If you compare the DPS result of Bless against no Bless and multiply the difference with the Full Cycle time you will get a number exact equal to 17*15%*125%. 17 is the average basic damage of Estoc, 125% because of the elemental lash.

 

4 How to Calculate DPS when dual wielding different weapons.

 

4.1 When dual wielding the same weapons

When you choose any weapon type with a “(2w)” before the name of weapon type in the Weapon Type column©, the DPS result you will get is based on dual wielding the exact same weapon, including the same weapon enchantment.

 

4.2 When dual wielding different weapons

Copy any existing line and paste it into line 11 and line 12.

In cell A 11, put in Main hand; In cell 11B, choose (2w)Dagger; Fill the cells in Weapon Enchantment column(H-Q), Talent column(R-AD), Equipment column (AE-AJ), the Character column (AK-AM), Other (AN-AS) and Speed Buff (AT-BH).

In cell A 12, put in off hand; In cell 12B, choose (2w)Sabre; Fill the cells in Weapon Enchantment column(H-Q), Talent column(R-AD), Equipment column (AE-AJ), the Character column (AK-AM), Other (AN-AS) and Speed Buff (AT-BH).

To get the result we want, put “=(E11*D11+E12*D12)/(D11+D12)” in cell E13 and I hope you know how to do the same for cell F13,G13.

 

5 Pierce/Slash

If you look into the "Enemy" Sheet, you will find the data of monsters. And most of the time when an enemy has a high pierce DR it also have a high pierce DR.

In a nutshell, the two weapon damage type of sword suck. Estoc's 5 DR bypass is more useful, which can be seen as a 29% damage bonus.

Edited April 10, 2017 by swapoer

[firkraag888]

this is pretty hardcore sh$t

[Boeroer]

Neat - but you see this (by the author of the Attack Speed Conundrum thread): https://naijaro.github.io/poe-speed-calculator/

 

Maybe you could combine those two things into one piece of software?

Edited April 5, 2017 by Boeroer

[swapoer]

Thanks for Maxquest's answer, I found out one of my error in the spreadsheet in regard of calculating attack time.

The spreadsheet is updated and I added several new section to the original post.

[Kaylon]

From your explanation it seems the way accuracy is affecting your damage is only an approximation.

 

Example:

 

15<your accuracy-enemy's defense<50 means you can never miss and every points of accuracy will decrease your number of grazes by 1, while increasing the number of criticals by 1.

ie over 100 attacks you will gain the difference in damage between a critical and a graze. This difference is 100% (which translates to an average 1% dmg increase) if you don't have any critical dmg bonus. If you have the Doemenel bonus, a durganized weapon and possibly the annihilation enchantment the difference between a critical and a graze becomes 210%, which means each point in accuracy will increase your average dmg by 2.1%...

[Boeroer]

Another thing: enchantments like overbearing or stunning do increase DPS. They lower enemy's defenses which is the same as an increase of ACC (for every party member who attacks that enemy, but let's put that aside).

So one could put that into consideration as well when calculating DPS. But I know it's hard to do because second hit roll and durations and stuff.

[swapoer]

From your explanation it seems the way accuracy is affecting your damage is only an approximation.

 

If you have the Doemenel bonus, a durganized weapon and possibly the annihilation enchantment the difference between a critical and a graze becomes 210%, which means each point in accuracy will increase your average dmg by 2.1%...

 

 

Yes. I do point that out that Critical Hit Modification will affect the effect of accuracy in PS2 of the section 3.1. 

 

1% is an approximation indeed, but it is easy to get the adjusted result with the actual Critical Hit Damage Bonus, since it is additive.

 

For me, narrowing down the rang into 15<your accuracy-enemy's defense<50 and figuring out this rule and the relation of it with critical hit modification give me an easy and practical way to assess the importance of accuracy.  

It give me an easier time because I can know for sure how the the accuracy bonus can be converted into my damage output. Whenever I see a accuracy bonus, I just convert it into a damage bonus by heart and get the feeling of how this bonus will benefit me.

 

Edited April 7, 2017 by swapoer

[swapoer]

Another thing: enchantments like overbearing or stunning do increase DPS. They lower enemy's defenses which is the same as an increase of ACC (for every party member who attacks that enemy, but let's put that aside).

So one could put that into consideration as well when calculating DPS. But I know it's hard to do because second hit roll and durations and stuff.

 

Actually, it is no impossible to simulate that situation.

I remember seeing a Tyranny spreadsheet which can simulate the situation in which you open your attack with a sunder followed by 9 auto attack. And the spreadsheet will give the simulation run for 100 times and get the average result.

I must admit I lack that kind of skill, although I know it is not too hard to achieve with the help of VBA.

[swapoer]

I have update the spreadsheet to include all the speed buff from Maxquest and also update the spreadsheet so it can calculate dual wielding DPS.

[Rooksx]

I think there's an error in the "Speed Mult" calculation. If you have enough speed buffs, this number can be negative which I assume shouldn't be right. I suspect there should be cap but don't understand what this calculation is meant to be doing. 

[Bizkit]

FYI, You didn't include the Sharp weapon enchantment, Weapon Specializations (although you can include the percentage in the DMG X column in the Other section for both of those) or the Confident Aim ability. There may be more you're missing as well.

Edited September 25, 2018 by Bizkit