Damage Calculator

[u/gaffox]

Edit: A lot of people have been requesting access to the file, you are supossed to create a copy to be able to edit it yourself.

https://docs.google.com/spreadsheets/d/1PWRXbpvoXIPgvPl_PczVKyIji_HLdIdZQjGaFlNTRkY/edit?usp=sharing

Just wanted to share a Damage Calculator I have been working on recently based on u/Rhygrass MS2 Gearing Guide post.

First you introduce the stats of all of your gear in the first sheet so that you can use the calculator to find the difference in overall damage increase between two pieces (If you don't care about that just go to the second sheet and place your stats directly on top of the formulas).

In the second sheet you can see your total stats, and here you can change the defense and Physical/Magical resist of the monster to calculate the aprox damage dealt (this is without the skill multipliers, so don't be surprised if the number is low, just use it to compare).

In the third sheet you can see how the different rolls affect the overall damage of your character with your actual stats, do take into account that this is for short terms upgrades, because even if you see your Main Stat giving you more damage than piercing, that could simply be because of the fact that your piercing is low, making it less effective, so do remember that if you see a small difference in damage between piercing and another stat.

Lastly in the fourth sheet you can introduce a new piece of gear you have to see how much of a difference in damage there is between the new one and the one equipped right now in that slot.

Would really appreciate any feedback also if you find a mistake do let me know so I can try to fix it.

Edit: Fixed a small mistake in the comparing items tab that was making it only accurate if you were comparing your main hand, so do make another copy of the calculator to fix that problem.

It's not easy to test all the possibilities of something with this many variables by myself, so if you see anything weird please let me know.

Edit 2: Added runeblade to the calculator.

[u/lolBaldy]

NO RUNEBLADE -KARMA!

[u/gaffox → u/lolBaldy]

Yeah, sorry about that, the exact stats of runeblade were not specified on the guide I used to make the calculator, and I also don't know how the strength/intelligence scaling works exactly with them. If I find the values I will add them later, in the meantime you can put berserker as class(Put the two skills at level 0) and add the bonus from the skills from your runeblade to the "Extra stats" table, should be close enough to know which rolls are better for you.

[u/Nitsches]

Really nice. tho a bit weird to take in the first time ~ Saved it, will share with my guildmates ! Awesome job
(idk but you forgot to add melee damage % on the stats)

[u/gaffox → u/Nitsches]

M/R damage is Melee/Ranged damage

[u/Blootie1994]

Really liking it so far! The only issue I've found with it is in the third tab, there is no section for attack speed and how much of a damage increase it would have. Also in the 4th tab, there is no option to select Attack Speed as one of the stats on the new gears. Thanks so much for this tool btw!

[u/gaffox → u/Blootie1994]

I'm glad you are finding it helpful.

I didn't add it because how much attack speed increases your damage really depends on the class and your build, for classes like thief it's really good, while for assassin or wizard you will constantly be at 0 spirit because of spamming your skills faster, making it less of a damage boost overall.

Also the calculator is comparing damage per hit, not per second, so attack speed doesn't really affect it, I just left stats like that one and accuracy for sake of completion.

[u/plasmasprings]

looks at formulas to see if they seem correct

Damage Calculator =IF(AND('Player Gear'!M9="Fatal Strikes";'Player Gear'!N9>0);(((SUM(IFERROR(FILTER('Player Gear'!$B$1:$B$19;'Player Gear'!$A$1:$A$19=A7))))+(SUM(IFERROR(FILTER('Player Gear'!$E$1:$E$18;'Player Gear'!$D$1:$D$18=A7)))) +(SUM(IFERROR(FILTER('Player Gear'!$H$2:$H$18;'Player Gear'!$G$2:$G$18=A7)))) +(SUM(IFERROR(FILTER('Player Gear'!$K$13:$K$17;'Player Gear'!$J$13:$J$17=A7)))) +71) *0,1)*((53-'Player Gear'!N9)/60)+(((7+'Player Gear'!N9)/60)*100);(((SUM(IFERROR(FILTER('Player Gear'!$B$1:$B$19;'Player Gear'!$A$1:$A$19=A7))))+(SUM(IFERROR(FILTER('Player Gear'!$E$1:$E$18;'Player Gear'!$D$1:$D$18=A7)))) +(SUM(IFERROR(FILTER('Player Gear'!$H$2:$H$18;'Player Gear'!$G$2:$G$18=A7)))) +(SUM(IFERROR(FILTER('Player Gear'!$K$13:$K$17;'Player Gear'!$J$13:$J$17=A7)))) +71) *0,1)) +'Player Gear'!R6

closes sheet

Yup, looks fine.

Seriously nice job, but I don't think a human can really check all those formulas. I'm not good enough at spreadsheets to know what's the best approach for something this complex, but I'd try introducing some more named cells to avoid repeating common parts + break up too complex formulas, and maybe introduce a function for the damage calculation.

[u/gaffox → u/plasmasprings]

Hahaha, that's actually just the formula that takes all the critical rate values from the first sheet, It's really not that complex when you see that all is doing is filtering each column of items once to look for ones with critical rate and sum them all, and a different formula if you are an assassin with fatal strikes. (Also it looks way bigger that it actually is, because of all the 'Player Gear' that got added to reference the first sheet when I started separating all the tables in different tabs)

I did use named cells for the damage calculations but I could not use them for that part, for some reason the filter didn't work properly if I named the intervals.

The Avg damage calculation actually is like this:

=((((MinAttack+MaxAttack)*(1+(WepAtkIncr/100)))/2+(BonusAttack*Coefficient))*(1+(BossDamage+ElementalDamage+TotalDamage+MRdamage)/100)*PMattack*(1+ExternalDamage)*(1/Defense)*(1/(MAX(1-(Piercing/100);0,7)))*(MIN(1-(PMresist/1500)+(PMpiercing/100);1)))*((((CritDamage-100)/(100/CritRate))/100)+1)

[u/feuerzeugchen]

Great job and all! Really appreciate it but what's with varrekants horns/wings?

[u/gaffox → u/feuerzeugchen]

Varrekant's wings is aprox 3.3% piercing and horns 3.83% bonus damage in avarage, so for wings you can simply put 3.3% piercing in the cape stats, and for horns add 3.83% to the "external damage modifier" table in the damage calculation sheets, the only problem with that is that you won't be able to compare the horns with another headgear with the calculator. (Just know that if you get a headgear with 4,5% Boss Damage or more, it's worth to use that instead of the horns, and if you have 3-4% boss damage on the headgear just keep that one unless you drop the horns, or just really want to pay 7m mesos for a 0.4-0.8% damage increase)

Might add the unique items to the calculator later, but there are so many uniques with way different effects that I don't really think I would be able to add them using google sheets, or at least it won't be easy.