Hey fellow Crusaders. Over the past few weeks I have been working on this little damage and toughness spreadsheet for Crusaders. It is mostly based on Loroeses Wizard spreadsheet, with alterations to account for Crusader actives, passives and the fact that we can wield a 2h and a shield.
As with Loroeses spreadsheet this is made mostly for Crusaders, but it should work for most other classes when not dual wielding.
On the first tab you can enter your current stats and on the second tab you can try out different items and see how they affect your damage and toughness. The third and fourth tab are where the calculations are done and the fifth tab has a little more information as to what should be entered where.
Please keep in mind that your toughness can be a bit off from your in game toughness. This is because I haven't found out how the melee and ranged damage reduction affixes are calculated in the total number.
Skill dmg doesn't show up, always 0, check your formula
Paragon is off...you didn't account for points spent in other categories so it doesn't show the right paragon level, something weird happens with the str/vit stats in the purple areas, this affects strength, armor, health, life everything...
Crit% is off
Crit%Dmg is off
IAS% is off (How could you forget Fervor for the buff 0/1 list?) 15%...
No CDR? One of the most important endgame stats for many builds? What..fervor users get 15% from it too?!
AllRes is off, something to do with how you calculated Extra Res to come up with Avg All Res is weird, I have around 1k res, not 1.7k
Why is your AllRes per socket showing 156 and not 78? Like why is it a sum? Something is really weird with your allres calcs
MH+OH doesn't sum the strength from each slot? MH+OH isn't summed at all? You can't delete the column without changing all the calcs..so make it functional?
Doing "per socket" for stats works but only if you somehow distinguish the wep socket from the rest...so that it multiplies 78*5 sockets for my chest/pants allres, w/o including my 6th socket, which is my CHD emerald in the wep...
Black Areas:
Eff Elite Dmg is showing as a negative value?
Unbuffed dps is showing as a neg value
Something is clearly weird with the Equipped Calcs page for a lot of the "important values"
I think a lot of the issues with the "green" values comes from the paragon issues. I'd start there with your fixes. After that I'd say avg allres calcs are your next issue.
This is just what I noticed from the initial look at it, just trying to help
It still needs a couple tweaks, but if you want to use it to try and fix yours, feel free. I'll probably post itin a separate thread later over the weekend.
It still needs a couple tweaks, but if you want to use it to try and fix yours, feel free. I'll probably post itin a separate thread later over the weekend.
I added Fervor to his version of your spreadsheet (at least attack speed wise) by adding the string "+'Equipped Items'!U17*15 " to the end of the appropriate field in the Equipped Calculations page for attack speed. Not sure if you'd care to add it to yours with changed U-field before you repost it in the separate thread but considering a lot of FoTH Crusaders are running Darklight it might be helpful for them. Just a thought! Have a great weekend and thanks for all your work for the community!
I'm definitely open to suggestions for what to add. So far I just added the obvious buffs, mainly that I use. I haven't played my crusader much at all though. I can also add a CDR row.
I'd def add a CDR row, even if you can't factor it into relative dps gains, you could set it up for people trying to reach the AC breakpoints. Fervor is the only thing that should prob be added to that effect (as it's a flat CDR/AS passive), the other passive buffs are specific skill %dmg increases or cool down reductions, and adding them in would take time and testing I think. But just having a way to sum CDR effectively across gear with or without Fervor within the spreadsheet would probably be helpful. That's all I have for now. Your spreadsheet is pretty much perfect otherwise and functions exceedingly well for multi-item comparisons/etc. should def make a new thread for it, no offense to Maikyl! Anyways cheers, I'm off to enjoy my Friday night!
Skill dmg doesn't show up, always 0, check your formula
Paragon is off...you didn't account for points spent in other categories so it doesn't show the right paragon level, something weird happens with the str/vit stats in the purple areas, this affects strength, armor, health, life everything...
Crit% is off
Crit%Dmg is off
IAS% is off (How could you forget Fervor for the buff 0/1 list?) 15%...
No CDR? One of the most important endgame stats for many builds? What..fervor users get 15% from it too?!
AllRes is off, something to do with how you calculated Extra Res to come up with Avg All Res is weird, I have around 1k res, not 1.7k
Why is your AllRes per socket showing 156 and not 78? Like why is it a sum? Something is really weird with your allres calcs
MH+OH doesn't sum the strength from each slot? MH+OH isn't summed at all? You can't delete the column without changing all the calcs..so make it functional?
Doing "per socket" for stats works but only if you somehow distinguish the wep socket from the rest...so that it multiplies 78*5 sockets for my chest/pants allres, w/o including my 6th socket, which is my CHD emerald in the wep...
Black Areas:
Eff Elite Dmg is showing as a negative value?
Unbuffed dps is showing as a neg value
Something is clearly weird with the Equipped Calcs page for a lot of the "important values"
I think a lot of the issues with the "green" values comes from the paragon issues. I'd start there with your fixes. After that I'd say avg allres calcs are your next issue.
This is just what I noticed from the initial look at it, just trying to help
First of all thank you for spotting all these things.
Now to clarify:
Skill damage is the flat damage you gain from passives, like holy cause, which is shown in game. I understand this can be confusing and will change it to account for skill damage from gear (note that this isn't shown on your character sheet).
Thanks for noting this, this is indeed a stupid mistake and I will fix this.
How exactly are Crit% and Crit Dmg% off?
I have looked through the passives list 3 times and still overlooked this.
I will add CDR to the spreadsheet.
Average All Resist is the average of Extra Resists and Resist All ((all resist*6+extra resistances)/6)
I messed something up here. Will see what I can do to change it.
I will see what I can do here (if you have say 3 diamonds in your chest, 2 diamonds in your legs and 1 ruby in both rings it should divide by 2, not by 4).
Eff Elite Dmg and Unbuffed dps doesn't show as a negative number on my end.
Could you post a link to your armory? With my own values it shows no actual issues.
I also intend to add more stats to account for the "new" stats, like area damage and resource cost reduction. I want this spreadsheet to show pretty much all information you also have in game and be able to compare items more precisely.
Skill dmg doesn't show up, always 0, check your formula
Paragon is off...you didn't account for points spent in other categories so it doesn't show the right paragon level, something weird happens with the str/vit stats in the purple areas, this affects strength, armor, health, life everything...
Crit% is off
Crit%Dmg is off
IAS% is off (How could you forget Fervor for the buff 0/1 list?) 15%...
No CDR? One of the most important endgame stats for many builds? What..fervor users get 15% from it too?!
AllRes is off, something to do with how you calculated Extra Res to come up with Avg All Res is weird, I have around 1k res, not 1.7k
Why is your AllRes per socket showing 156 and not 78? Like why is it a sum? Something is really weird with your allres calcs
MH+OH doesn't sum the strength from each slot? MH+OH isn't summed at all? You can't delete the column without changing all the calcs..so make it functional?
Doing "per socket" for stats works but only if you somehow distinguish the wep socket from the rest...so that it multiplies 78*5 sockets for my chest/pants allres, w/o including my 6th socket, which is my CHD emerald in the wep...
Black Areas:
Eff Elite Dmg is showing as a negative value?
Unbuffed dps is showing as a neg value
Something is clearly weird with the Equipped Calcs page for a lot of the "important values"
I think a lot of the issues with the "green" values comes from the paragon issues. I'd start there with your fixes. After that I'd say avg allres calcs are your next issue.
This is just what I noticed from the initial look at it, just trying to help
First of all thank you for spotting all these things.
Now to clarify:
Skill damage is the flat damage you gain from passives, like holy cause, which is shown in game. I understand this can be confusing and will change it to account for skill damage from gear (note that this isn't shown on your character sheet).
Thanks for noting this, this is indeed a stupid mistake and I will fix this.
How exactly are Crit% and Crit Dmg% off?
I have looked through the passives list 3 times and still overlooked this.
I will add CDR to the spreadsheet.
Average All Resist is the average of Extra Resists and Resist All ((all resist*6+extra resistances)/6)
I messed something up here. Will see what I can do to change it.
I will see what I can do here (if you have say 3 diamonds in your chest, 2 diamonds in your legs and 1 ruby in both rings it should divide by 2, not by 4).
Eff Elite Dmg and Unbuffed dps doesn't show as a negative number on my end.
Could you post a link to your armory? With my own values it shows no actual issues.
I also intend to add more stats to account for the "new" stats, like area damage and resource cost reduction. I want this spreadsheet to show pretty much all information you also have in game and be able to compare items more precisely.
My battlenet name is in my icon?
But honestly like it's not even worth it at this point. Loroese's Crusader version of his own spreadsheet is superior in every way. And I mean you've pretty much just unknowingly redone all the work he had already done anyways. I don't mean to belittle the work you did retooling a lot of the spreadsheet to go from Wizard to Crusader, but honestly I don't really think there's a point going back to fix all the errors in your version when his already works "out of the box". Instead maybe join his thread and pair up with him to help expand his original version of the spreadsheet to include more information (like Fervor, CDR, dmg reduction/etc.)? I'm sorry. It is what it is. You had a good thought in expanding on his work for Wizards (to give something to the Crusader community) but it's not like you could have known he had a Crusader version already started.
Again, thanks for your effort, but definitely get in contact with Loroese.
Skill dmg doesn't show up, always 0, check your formula
Paragon is off...you didn't account for points spent in other categories so it doesn't show the right paragon level, something weird happens with the str/vit stats in the purple areas, this affects strength, armor, health, life everything...
Crit% is off
Crit%Dmg is off
IAS% is off (How could you forget Fervor for the buff 0/1 list?) 15%...
No CDR? One of the most important endgame stats for many builds? What..fervor users get 15% from it too?!
AllRes is off, something to do with how you calculated Extra Res to come up with Avg All Res is weird, I have around 1k res, not 1.7k
Why is your AllRes per socket showing 156 and not 78? Like why is it a sum? Something is really weird with your allres calcs
MH+OH doesn't sum the strength from each slot? MH+OH isn't summed at all? You can't delete the column without changing all the calcs..so make it functional?
Doing "per socket" for stats works but only if you somehow distinguish the wep socket from the rest...so that it multiplies 78*5 sockets for my chest/pants allres, w/o including my 6th socket, which is my CHD emerald in the wep...
Black Areas:
Eff Elite Dmg is showing as a negative value?
Unbuffed dps is showing as a neg value
Something is clearly weird with the Equipped Calcs page for a lot of the "important values"
I think a lot of the issues with the "green" values comes from the paragon issues. I'd start there with your fixes. After that I'd say avg allres calcs are your next issue.
This is just what I noticed from the initial look at it, just trying to help
First of all thank you for spotting all these things.
Now to clarify:
Skill damage is the flat damage you gain from passives, like holy cause, which is shown in game. I understand this can be confusing and will change it to account for skill damage from gear (note that this isn't shown on your character sheet).
Thanks for noting this, this is indeed a stupid mistake and I will fix this.
How exactly are Crit% and Crit Dmg% off?
I have looked through the passives list 3 times and still overlooked this.
I will add CDR to the spreadsheet.
Average All Resist is the average of Extra Resists and Resist All ((all resist*6+extra resistances)/6)
I messed something up here. Will see what I can do to change it.
I will see what I can do here (if you have say 3 diamonds in your chest, 2 diamonds in your legs and 1 ruby in both rings it should divide by 2, not by 4).
Eff Elite Dmg and Unbuffed dps doesn't show as a negative number on my end.
Could you post a link to your armory? With my own values it shows no actual issues.
I also intend to add more stats to account for the "new" stats, like area damage and resource cost reduction. I want this spreadsheet to show pretty much all information you also have in game and be able to compare items more precisely.
My battlenet name is in my icon?
But honestly like it's not even worth it at this point. Loroese's Crusader version of his own spreadsheet is superior in every way. And I mean you've pretty much just unknowingly redone all the work he had already done anyways. I don't mean to belittle the work you did retooling a lot of the spreadsheet to go from Wizard to Crusader, but honestly I don't really think there's a point going back to fix all the errors in your version when his already works "out of the box". Instead maybe join his thread and pair up with him to help expand his original version of the spreadsheet to include more information (like Fervor, CDR, dmg reduction/etc.)? I'm sorry. It is what it is. You had a good thought in expanding on his work for Wizards (to give something to the Crusader community) but it's not like you could have known he had a Crusader version already started.
Again, thanks for your effort, but definitely get in contact with Loroese.
Looks like I read your post after I uploaded it. I tested your stats in the spreadsheet and it gives me slightly lower dps than your armory, but I can't see how you spent your paragon points so it seems fine. Not sure what went wrong there, but it seems fixed now with the reworked version.
I will wait and see how it works now for people and try to add more things like thorns damage and maybe support for more passives.
I will wait and see how it works now for people and try to add more things like thorns damage and maybe support for more passives.
Why? Did you miss the point that your spreadsheet isn't a significant variation on Loroese's? What is the point of continuing to try and build on your copy of his when you could just significantly contribute to his by working with him? Maintaining this thread seems pointless when you could accomplish more by working with him on new features for the spreadsheet. Please try to get in contact with Loroese through his topic and "merge" (not much is diff) your versions and then go from there?
Personally, I don't mind if someone wants to make something similar to my spreadsheet with whatever differences they prefer. It does take a bit of work to maintain, especially when patches come around, but I don't see anything wrong with sharing a variant of the spreadsheet if it has features that are new or different from mine.
If you do want to maintain and work on a spreadsheet, I recommend working out the bugs using your character in game. Armory profiles won't really help because they don't always show accurate gear stats, and it's just easier to equip gear yourself to troubleshoot the calculations.
Quote from Hjalmr» Why? Did you miss the point that your spreadsheet isn't a significant variation on Loroese's? What is the point of continuing to try and build on your copy of his when you could just significantly contribute to his by working with him? Maintaining this thread seems pointless when you could accomplish more by working with him on new features for the spreadsheet. Please try to get in contact with Loroese through his topic and "merge" (not much is diff) your versions and then go from there?
You're not doing anyone any favors by trying to maintain your spreadsheet as separate from his...how much more simply can I explain this?
I think you missed my point there. If I had read your post BEFORE I had uploaded the completely revamped version or even started work on it, I wouldn't have bothered. However, Seeing as you 7 hours away from me (assuming from your battle.net profile), I started work on it while you were sleeping.
Seeing as the spreadsheet was finished before I read your post I figured I might as well update the link in the OP and see if this one is any better.
Personally, I don't mind if someone wants to make something similar to my spreadsheet with whatever differences they prefer. It does take a bit of work to maintain, especially when patches come around, but I don't see anything wrong with sharing a variant of the spreadsheet if it has features that are new or different from mine.
If you do want to maintain and work on a spreadsheet, I recommend working out the bugs using your character in game. Armory profiles won't really help because they don't always show accurate gear stats, and it's just easier to equip gear yourself to troubleshoot the calculations.
Here is where the issue was I think. I tested it for myself with several sets equipped and it worked. I can test only so many times myself, so at some point other people will have to just try it out. In the case of the first spreadsheet apparently something went wrong, which I still haven't figured out. The second spreadsheet, however, does seem to work, judging from someones battle.net profile and again several gear sets of my own.
As you said in your own spreadsheets thread, I plan on adding features you don't plan on, so I don't see why these spreadsheets can run alongside eachother for the time being. If it turns out mine still contains a ton of bugs, I will probably contact you again to see if I can help with your spreadsheet.
I just spent a few minutes comparing your spreadsheet results to mine, and it looks like it works pretty well, but I noticed the following:
The green cells in the Gear Comparison tab use the Equipped Calculations data.
The elite damage reduction does not appear to work properly. If I put 10 in on set, it shows as 1000%.
Also, each source of elite damage reduction works as its own multiplicative damage reduction, similar to CDR and CR.
I noticed you changed the damage reduction formulas to account for a lvl 70 mob, whereas I have it as lvl 73. That will get the toughness value to match, but do you have any indication of what the actual mob levels are? I have no idea for sure so I just left it as 3+max level.
My only other comment is that if you want to report toughness, as the cells are described, then I recommend using the actual formula for toughness, which will include elite, melee, and ranged damage reduction, in addition to the averaged resists, in some manner. As it stands, the spreadsheet calculates some resist averaged EHP, so stating Toughness and Elite Toughness is a little misleading.
I just spent a few minutes comparing your spreadsheet results to mine, and it looks like it works pretty well, but I noticed the following:
The green cells in the Gear Comparison tab use the Equipped Calculations data.
The elite damage reduction does not appear to work properly. If I put 10 in on set, it shows as 1000%.
Also, each source of elite damage reduction works as its own multiplicative damage reduction, similar to CDR and CR.
I noticed you changed the damage reduction formulas to account for a lvl 70 mob, whereas I have it as lvl 73. That will get the toughness value to match, but do you have any indication of what the actual mob levels are? I have no idea for sure so I just left it as 3+max level.
My only other comment is that if you want to report toughness, as the cells are described, then I recommend using the actual formula for toughness, which will include elite, melee, and ranged damage reduction, in addition to the averaged resists, in some manner. As it stands, the spreadsheet calculates some resist averaged EHP, so stating Toughness and Elite Toughness is a little misleading.
Good point on the Toughness, however I have no idea how elite, melee and ranged reductions are calculated into toughness. It's a smaller percentage than the actual reduction, but I haven't been able to find the formula for that yet.
I must have overlooked Elite Damage Reduction when converting everything to percentages, this is fixed now
I put level 70 there to show it as your toughness, I don't have any idea, however, what level mobs are. I assume they are 73 on all Torment levels. Changed Toughness to level 73 EHP for the time being. I hope I can find the formulas for elite, melee and ranged damage reduction, so I can show both EHP and Toughness.
I tried for a little while to get a toughness formula but in the end couldn't figure out what the weights are for melee, ranged, and elite. It could probably be done with some sort of fit analysis, if you wanted ideas on how to find the correct formula.
As with Loroeses spreadsheet this is made mostly for Crusaders, but it should work for most other classes when not dual wielding.
On the first tab you can enter your current stats and on the second tab you can try out different items and see how they affect your damage and toughness. The third and fourth tab are where the calculations are done and the fifth tab has a little more information as to what should be entered where.
Please keep in mind that your toughness can be a bit off from your in game toughness. This is because I haven't found out how the melee and ranged damage reduction affixes are calculated in the total number.
Here is the spreadsheet for everyone to use:https://drive.google.com/file/d/0B3cvC0buyvdiZWlKZ0pWUGFpOWs/edit?usp=sharing
I hope you guys find this usefull and if I missed something or if you find any bugs please post them in this topic or pm me.
Changelog in Spreadsheet
Green Areas:
This is just what I noticed from the initial look at it, just trying to help
https://drive.google.com/file/d/0B4-opYZrg_XRVERGYXA4eXNQRVE/edit?usp=sharing
It still needs a couple tweaks, but if you want to use it to try and fix yours, feel free. I'll probably post itin a separate thread later over the weekend.
Crusader DPS and EHP Spreadsheet, meant for Crusaders
My Wizard
Crusader DPS and EHP Spreadsheet, meant for Crusaders
My Wizard
Now to clarify:
I also intend to add more stats to account for the "new" stats, like area damage and resource cost reduction. I want this spreadsheet to show pretty much all information you also have in game and be able to compare items more precisely.
But honestly like it's not even worth it at this point. Loroese's Crusader version of his own spreadsheet is superior in every way. And I mean you've pretty much just unknowingly redone all the work he had already done anyways. I don't mean to belittle the work you did retooling a lot of the spreadsheet to go from Wizard to Crusader, but honestly I don't really think there's a point going back to fix all the errors in your version when his already works "out of the box". Instead maybe join his thread and pair up with him to help expand his original version of the spreadsheet to include more information (like Fervor, CDR, dmg reduction/etc.)? I'm sorry. It is what it is. You had a good thought in expanding on his work for Wizards (to give something to the Crusader community) but it's not like you could have known he had a Crusader version already started.
Again, thanks for your effort, but definitely get in contact with Loroese.
I will wait and see how it works now for people and try to add more things like thorns damage and maybe support for more passives.
http://www.diablofans.com/forums/diablo-iii-class-forums/crusader-the-church-of-zakarum/88848-crusader-dps-and-ehp-spreadsheet
You're not doing anyone any favors by trying to maintain your spreadsheet as separate from his...how much more simply can I explain this?
If you do want to maintain and work on a spreadsheet, I recommend working out the bugs using your character in game. Armory profiles won't really help because they don't always show accurate gear stats, and it's just easier to equip gear yourself to troubleshoot the calculations.
Crusader DPS and EHP Spreadsheet, meant for Crusaders
My Wizard
Seeing as the spreadsheet was finished before I read your post I figured I might as well update the link in the OP and see if this one is any better.
Here is where the issue was I think. I tested it for myself with several sets equipped and it worked. I can test only so many times myself, so at some point other people will have to just try it out. In the case of the first spreadsheet apparently something went wrong, which I still haven't figured out. The second spreadsheet, however, does seem to work, judging from someones battle.net profile and again several gear sets of my own.
As you said in your own spreadsheets thread, I plan on adding features you don't plan on, so I don't see why these spreadsheets can run alongside eachother for the time being. If it turns out mine still contains a ton of bugs, I will probably contact you again to see if I can help with your spreadsheet.
Also, why don't quotes work properly anymore?
I noticed you changed the damage reduction formulas to account for a lvl 70 mob, whereas I have it as lvl 73. That will get the toughness value to match, but do you have any indication of what the actual mob levels are? I have no idea for sure so I just left it as 3+max level.
My only other comment is that if you want to report toughness, as the cells are described, then I recommend using the actual formula for toughness, which will include elite, melee, and ranged damage reduction, in addition to the averaged resists, in some manner. As it stands, the spreadsheet calculates some resist averaged EHP, so stating Toughness and Elite Toughness is a little misleading.
Crusader DPS and EHP Spreadsheet, meant for Crusaders
My Wizard
I must have overlooked Elite Damage Reduction when converting everything to percentages, this is fixed now
I put level 70 there to show it as your toughness, I don't have any idea, however, what level mobs are. I assume they are 73 on all Torment levels. Changed Toughness to level 73 EHP for the time being. I hope I can find the formulas for elite, melee and ranged damage reduction, so I can show both EHP and Toughness.
Crusader DPS and EHP Spreadsheet, meant for Crusaders
My Wizard
Note that Legedary Items is a work in progress, the only usefull thing there is the enchantress legendary.