The Great Big Powerset Damage Spreadsheet of Doom
By
Bored,
Posted on: 2008-06-03 21:33 in Player Guides
You might be a genius.
Thanks for doing this!
In the interest of tl/dr, does your sheet easily apply NPC +RES?
If not, you or someone else out there should really add or integrate that functionality. 
I know that there's a huge +RES excel sheet out there somewhere...from late '06 maybe?
Repeat Offenders
It won't do resistance or resistance debuffs. It'd be fairly easy to add, I think, with one caveat: the spreadsheet doesn't track separate damage types except for fire (because Fiery Embrace is weird), so it'd have to be a blanket resistance.
The enemy resistance spreadsheet (most recent update) is Culex's and seems to be updated fairly regularly.
Really cool spreadsheet. Thanks for the hard work it must have taken to put this together.
For those of us without Excel, it unfortunately doesn't quite work right in OpenOffice Calc. In OpenOffice 2.2, it didn't work at all. So I upgraded to OpenOffice 2.4 and it mostly works. For some reason the scrapper, stalker and SoA sets don't seem to work right. It gives Err:502 for the damage scale for the powers.
Now of course the next thing I need is an attack chain calculator. Where do I get one of those 
[ QUOTE ]
So I upgraded to OpenOffice 2.4 and it mostly works. For some reason the scrapper, stalker and SoA sets don't seem to work right. It gives Err:502 for the damage scale for the powers.
[/ QUOTE ]
...huh. So it does. That is really peculiar and I have no idea why. If it were doing it for *all* sets that would at least make some degree of sense. I'll see if I can figure it out and upload a new version. I've got cast time updates and some other changes made and waiting to be posted as well.
Edit: Found it! I got lazy and left a bunch of fields blank for things like bonus DoT and fire damage crits for sets that lack them. Excel either ignores blank fields or treats them as zero if they get referenced in a lookup table, but OpenOffice coughs up an error. I'll get that fixed in the next day or two.
Version 1.1 is now available from the link in the original post.
Changes:
* Fixed an error that caused some sets to spazz out in OpenOffice.
* Controller > Stone Mastery > Hurl Boulder cast time corrected.
* Martial Arts cast times updated according to the current test patch.
* One Thousand Cuts cast time corrected.
* Missing DPA formulas in the Sandbox section added.
* Fixed missing text in the Critical/Containment/Scourge box comments.
* Damage/Endurance/Recharge estimator now handles AoE attacks and can be set to use either the "classic" formula or the experimental formulas that factor in cast time.
* Damage slotting is now a free-entry box. Values over 95 (the ED soft cap) are supported but not recommended.
* Rearranged the configuration options. Hopefully they flow more logically.
Awesome! Thanks for the quick fix. I'll have to check it out when I get home from work.
Noice! GJ
Finally got a chance to download and play with the spreadsheet. Works great in OO 2.4 now. It's great fun to be able to see what powerset would look like on other ATs.
I also like the dam/end/recharge calculator based on the current and future(?) balance formulas. Of course, it would also be nice to have the powers show their actual recharge and end use for comparison. Maybe is 1.2?
Again, thanks for all the hard work in putting this together.
New version is now available from the link in the original post.
Version 1.2
*Energy Transfer activation time changed to 2.67. Affected sets:
Brute > Energy Melee
Stalker > Energy Melee
Tanker > Energy Melee
*Activation time for all powers using the Brawl animation changed to 0.83. Affected sets:
Blaster > Electricity Manipulation > Charged Brawl
Blaster > Energy Manipulation > Energy Punch
Brute > Dark Melee > Shadow Punch
Brute > Energy Melee > Energy Punch
Brute > Electric Melee > Charged Brawl
Scrapper > Dark Melee > Shadow Punch
Stalker > Dark Melee > Shadow Punch
Stalker > Energy Melee > Energy Punch
Stalker > Electric Melee > Charged Brawl
Tanker > Dark Melee > Shadow Punch
Tanker > Energy Melee > Energy Punch
*Fixed a problem with some Scrapper Fire Melee powers that incorrectly doubled their critical damage
*Fixed a problem with Blaster/Corruptor > Assault Rifle > Flamethrower that caused it to display 0 damage
*Added support for real-world cast time estimations in the DPA section, per Arcanaville's formula
Whoa, that's awesome!
You had me at "spreadsheet."
My mind wanders so often you've probably seen its picture on milk cartons. - Me... the first person version of the third person Steelclaw
To fix for Castle's most recent post,
Go to Sheet 2 and change Kheldian melee/range mod to 0.85/0.8 and Kheldian Dwarf melee mod to 1.0
Go to Sheet 4 and change:
War Mace DS -> Clobber to 2.29
Energy Melee DS -> Barrage to 1.32
Don't know the total DS for Midnight Grasp though.
I've got updates for claws and a few other things, but I've been putting off publishing an update until I13 patch notes are up, mostly because trying to work the claws DPA functions into the damage calculator is resulting in formulas that, viewed from the right angle, will summon Cthulu.
[ QUOTE ]
I've got updates for claws and a few other things, but I've been putting off publishing an update until I13 patch notes are up, mostly because trying to work the claws DPA functions into the damage calculator is resulting in formulas that, viewed from the right angle, will summon Cthulu.
[/ QUOTE ]
you say that as if it's a bad thing...
Thanks for this Wonderslug! Wish I had noticed it sooner.
I haven't looked at your file, but I can say, you have FAR too much free time.
[ QUOTE ]
This started out, about a month ago, as a quick little tool to let me see what would happen to one powerset with some modifier tweaks. Partially inspired by Arcanaville's Every Armorset etc. spreadsheet, it quickly went from a simple "What if I..." to "Let's learn about the intricacies of Excel by leaping head-first into shark-infested waters." Specifically, it turned into a tool to look at every damaging powerset across all archetypes, with options to muck around with modifiers and damage buffs. No doubt Excel gurus can find things I could have done more simply or more elegantly, but considering the most complex thing I've done with a spreadsheet in the past was a gradebook with weighted averages, I think it turned out okay.
As for what you might want to use it for, well, there's always poking around with likely powerset proliferation candidates. It's a good way to get a general overview of several powersets in rapid succession, compared to trawling through City of Data or futzing around with builds in Mids. And, if you're inclined to ponder suggestions like "Increase the damage modifiers for Archetype X from foo to bar, well, here's an easy way to tell roughly what that suggestion will actually do, which is what started me down this path of madness in the first place.
The information in this spreadsheet is current and correct (I hope) as of the time of this writing. The bulk of the data came from City of Data, last updated with the release of I12. In some cases the information available from City of Data has already been rendered out of date by patches or impending patches, and I have included the most current data, based on patch notes, dev posts, the in-game "real numbers," or reliable testing, to the best of my knowledge. That being said, there's gotta be at least a few errors in this thing, so feel free to point 'em out.
What this spreadsheet does
<ul type="square"> [*] Calculates the damage of every damage-dealing power in every player powerset (unless I missed some), for any archetype (including henchmen and pets), for levels 1-50.
[*] Calculates DPA for each attack (excluding rain/location AoE powers). Toggle powers and effects that don't really have an activation time per se are assumed to have an activation time of 1--that is, DPA = Damage. That makes sense to me, but if there's a better option I'd be glad to hear it.
[*] Calculates the effect of nearly every self-affecting damage buff in the game. I may have missed some, and there's no specific handling for Fury, inspirations, IO set bonuses, Kheldian link bonuses, or veteran kamikaze--I mean, buff pets. There is, however, a box you can plug extra damage buff numbers into, so knock yourself out.
[*] Handles pets and henchmen.
[*] Provides options for dealing with bonus DoT: none, average, or maximum. This applies to "cancel on miss" DoT that may or may not occur; guaranteed DoT is rolled into the base damage of powers.
[*] Provides a sandbox for hypothetical powers or comparing values plugged in from different powersets.
[*] Calculates damage/endurance/recharge, given any one of those three, based on the standard damage formula.[/list]
What this spreadsheet does, but not especially elegantly
<ul type="square"> [*] Critical hit/containment/scourge damage. This is an on-or-off setting, because the conditions required are so widely varied between archetypes and/or difficult to average realistically.
[*] Powers that use "pseudo-pets" to deal damage. Powers that apply a single shot of damage, like Fallout, Lightning Rod, or Trip Mine, are grouped together in the "Petlike Summons" category. Because these powers use pet damage modifiers, there's no way to include them in the normal AT powerset display without a ton of ugly kludges and/or hardcoding, both of which I want to avoid (and believe me, some of those formulas are very ugly already). Powers that create a persistent entity that deals damage at specified intervals, like Burn or Blizzard, function so differently from normal powers that the only real way to deal with them was to give them their own section. (Technically, some of the "single shot" powers I listed above function this way too, but that way madness lies. Just pretend the division is nice and neat and we'll gloss over it because the end result is the same, but with less gibbering along the way.)[/list]
What this spreadsheet does not do (yet)
<ul type="square"> [*] Damage procs. Should be relatively easy to add if people want it.
[*] Temporary powers. Ditto.
[*] Scale with multiple targets for AoE powers. May involve some gibbering on my part, but doable.
[*] Attack chains. You're on your own for that.
[*] Resistance or resistance debuffs. Unlikely to change.[/list]
Using the spreadsheet
To use the basic functionality of the spreadsheet the only things you actually need to bother with are the three big pulldown menus along the top. The first one is for archetype, which determines the modifiers that are used. The second is for archetype powerset groups (blaster sets, dominator sets, etc.), and the third selects individual powersets within those groups. Pets and mastermind henchmen are all found in the "Pets" category of the second menu, and should use the Pet or various Henchman archetypes if you want to see how much damage they'll actually do in game.
Green boxes will accept any number typed into them; as a general rule these should probably be sane numbers, but if you feel pathologically compelled to see what happens to Energy Transfer with a melee damage modifier of 6,537, don't let me stop you.
Most of the sheet should be self explanatory, and it's fairly liberally commented, so check the comment boxes for additional guidance.
Where the heck is it?
Right here.
Other notes
The spreadsheet appears to be fully functional in Open Office, although it's rather ugly and some of the conditional formatting goes something something. It also seems to insist on being opened in read-only mode, which is a bit of a problem since the sheet won't actually work in read-only mode so far as I can tell; you'll have to flip it to edit mode to, say, use the pulldown menus.
Some of the formulas used in this sheet are spectacularly ugly. They all appear to work properly, but it's always possible there's some corner case somewhere that makes everything go wonky. That would be good to know about, if it happens.
Aside from data provided by Red Tomax via City of Data, this spreadsheet makes use of information and formulas posted by iakona, Arcanaville, Stupid_Fanboy, BackAlleyBrawler, and probably several other people I'm forgetting about. And also pohsyb, after a fashion, what with the in-game numbers and all. So, thanks.
[/ QUOTE ]
Updated version for i15
<ul type="square">[*]Dominator modifiers updated[*]Dominator assault set and Gravity Control > Lift attack values updated[*]Domination removed from damage buff selector[*]Dark Melee > Siphon Life and Midnight Grasp updated[*]Soul Drain split from Sunless Mire and updated in damage buff selector[*]War Mace > Clobber updated[*]Energy Melee > Barrage updated [*]Fixed typo in Icy Assault > Bitter Ice Blast damage[*]Tanker Pyre Mastery powers now treat fire damage as fire damage[*]Pyre Mastery > Char damage value updated[*]Wolf Spider > Single Shot/Venom Grenade/Frag Grenade cast times updated[*]Blaster and Corruptor Assault Rifle cast times updated[*]Stalker epic snipes changed from range scale to melee scale[*]Black Dwarf Mire and Drain cast times corrected (they were swapped)[*]Fifth APP and PPP powers added where applicable[*]Damage/endurance/recharge calculator for claws formulas (single-target only) added[/list]
[ QUOTE ]
Updated version for i15
<ul type="square">[*]Dominator modifiers updated[*]Dominator assault set and Gravity Control > Lift attack values updated[*]Domination removed from damage buff selector[*]Dark Melee > Siphon Life and Midnight Grasp updated[*]Soul Drain split from Sunless Mire and updated in damage buff selector[*]War Mace > Clobber updated[*]Energy Melee > Barrage updated [*]Fixed typo in Icy Assault > Bitter Ice Blast damage[*]Tanker Pyre Mastery powers now treat fire damage as fire damage[*]Pyre Mastery > Char damage value updated[*]Wolf Spider > Single Shot/Venom Grenade/Frag Grenade cast times updated[*]Blaster and Corruptor Assault Rifle cast times updated[*]Stalker epic snipes changed from range scale to melee scale[*]Black Dwarf Mire and Drain cast times corrected (they were swapped)[*]Fifth APP and PPP powers added where applicable[*]Damage/endurance/recharge calculator for claws formulas (single-target only) added[/list]
[/ QUOTE ]
Thanks wonderslug
This started out, about a month ago, as a quick little tool to let me see what would happen to one powerset with some modifier tweaks. Partially inspired by Arcanaville's Every Armorset etc. spreadsheet, it quickly went from a simple "What if I..." to "Let's learn about the intricacies of Excel by leaping head-first into shark-infested waters." Specifically, it turned into a tool to look at every damaging powerset across all archetypes, with options to muck around with modifiers and damage buffs. No doubt Excel gurus can find things I could have done more simply or more elegantly, but considering the most complex thing I've done with a spreadsheet in the past was a gradebook with weighted averages, I think it turned out okay.
As for what you might want to use it for, well, there's always poking around with likely powerset proliferation candidates. It's a good way to get a general overview of several powersets in rapid succession, compared to trawling through City of Data or futzing around with builds in Mids. And, if you're inclined to ponder suggestions like "Increase the damage modifiers for Archetype X from foo to bar, well, here's an easy way to tell roughly what that suggestion will actually do, which is what started me down this path of madness in the first place.
The information in this spreadsheet is current and correct (I hope) as of the time of this writing. The bulk of the data came from City of Data, last updated with the release of I12. In some cases the information available from City of Data has already been rendered out of date by patches or impending patches, and I have included the most current data, based on patch notes, dev posts, the in-game "real numbers," or reliable testing, to the best of my knowledge. That being said, there's gotta be at least a few errors in this thing, so feel free to point 'em out.
What this spreadsheet does
What this spreadsheet does, but not especially elegantly
What this spreadsheet does not do (yet)
Using the spreadsheet
To use the basic functionality of the spreadsheet the only things you actually need to bother with are the three big pulldown menus along the top. The first one is for archetype, which determines the modifiers that are used. The second is for archetype powerset groups (blaster sets, dominator sets, etc.), and the third selects individual powersets within those groups. Pets and mastermind henchmen are all found in the "Pets" category of the second menu, and should use the Pet or various Henchman archetypes if you want to see how much damage they'll actually do in game.
Green boxes will accept any number typed into them; as a general rule these should probably be sane numbers, but if you feel pathologically compelled to see what happens to Energy Transfer with a melee damage modifier of 6,537, don't let me stop you.
Most of the sheet should be self explanatory, and it's fairly liberally commented, so check the comment boxes for additional guidance.
Where the heck is it?
Right here.
Other notes
The spreadsheet appears to be fully functional in Open Office, although it's rather ugly and some of the conditional formatting goes something something. It also seems to insist on being opened in read-only mode, which is a bit of a problem since the sheet won't actually work in read-only mode so far as I can tell; you'll have to flip it to edit mode to, say, use the pulldown menus.
Some of the formulas used in this sheet are spectacularly ugly. They all appear to work properly, but it's always possible there's some corner case somewhere that makes everything go wonky. That would be good to know about, if it happens.
Aside from data provided by Red Tomax via City of Data, this spreadsheet makes use of information and formulas posted by iakona, Arcanaville, Stupid_Fanboy, BackAlleyBrawler, and probably several other people I'm forgetting about. And also pohsyb, after a fashion, what with the in-game numbers and all. So, thanks.