Whirlin's Damage/Crit Comparing Spreadsheet

[Whirlin]

ATTACHED AS ZIP


Hosted File on Filedropper... I tried Google Docs, but there are two excel features incompatible with google docs that I utilize in this spreadsheet:

Conditional Dropdown menus to help manage an excessively long list of Weapons. Selecting the Weapon Type will automatically filter the Weapon Selection
Conditional Formatting. I have it set so that each critical outcome that has a FATAL condition along with it is highlighted in RED for that particular critical table.


Compatibility:
This should work for anything 2007 onward that can take the xlsx file format. This may function appropriately in limited capacity with 2003 and previously, as I specifically did not use equations introduced after 2003. But, I only have 2007, 2010, 2013 available to me for testing at the moment, so anyone with 2003 is welcome to chime in!

Features:

Full dump of all known weapon types, damage factors, and critical tables utilized for all AS/DS resolved attacks.

Some breakdown of critical tables associated with certain weapon types are still unknown...
Some critical tables require updates for AS/DS resolved attacks, such as disintegration critical tables.


Optional AvD bonuses for Perfect Weapons, DF bonuses for perfects/Paladins
Crit Weighting, Damage Weighting, Crit Padding, Damage Padding
Utilized the post https://gswiki.play.net/mediawiki/index.php/Unaimed_Body_Location_Assignment_%28saved_post%29 to determine base aiming stats on critters, but you can set your own AIMING to adjust these values!
Added CHANNEL option for BOLT spells to adjust critical tables and accuracy


What it does:

YOU:

Input your Weapon, AS, Damage Weighting, Critical Weighting, Phantom Critical Weighting, AvD Bonus (if applicable), DF Bonus (if applicable), and whether or not you're aiming, where you're aiming, and aiming accuracy %
Target's DS, Armor Yype, Damage Padding, Critical Padding


The Spreadsheet will tell you

Average Blood Loss per attack
Breakdown of all potential body parts/critical rank outcomes for each critical type (if applicable)
Which critical ranks have a Fatal outcome
Overall chance of critical death based on the above factors
Average Blood loss resulting from criticals
Overall Damage per swing, and chance of death.



Why use it?

Is this weapon better/worse than my current weapon? How much so?
What is one enchant worth to my current hunting condition?
Why should wizards be able to aim spells?
What can Whirlin REALLY do with Excel? We've heard a lot, but seen nothing!


What else needs to be done til it's officially complete?

Need more data on critical breakdowns of unknown weapons.
Add in a second Character/Critter option for true side-by-side comparison purposes.
-YOUR ADVICE HERE-

[lordsmo]

Gave it a very superficial once-over, but so far it looks good.

Some input: in terms of the target location probabilities, that link has two sets: one for corp, and one for non-corp. The corp one has a larger sample size, but is potentially skewed by "critting out" of target locations. The non-corp one has a smaller sample size, but can't be skewed by the critting out phenomenon.

It seems you are using the values from the corporeal set. Do you think that there really is a different random target distribution for corp and non-corp? Do you think the sample size for the non-corp is too small? My guess would be "no" for both, in which case the non-corp set might be better to use. I'm guessing though, and your guess might be better than mine. In any event, the difference will be extremely small.

[Geijon Khyree]

Hell yeah Whirlin!

[Vorpodu]

I'm really surprised and disappointed there isn't something about the legendary bow in all that text.

[Whirlin]

Gave it a very superficial once-over, but so far it looks good.

Some input: in terms of the target location probabilities, that link has two sets: one for corp, and one for non-corp. The corp one has a larger sample size, but is potentially skewed by "critting out" of target locations. The non-corp one has a smaller sample size, but can't be skewed by the critting out phenomenon.

It seems you are using the values from the corporeal set. Do you think that there really is a different random target distribution for corp and non-corp? Do you think the sample size for the non-corp is too small? My guess would be "no" for both, in which case the non-corp set might be better to use. I'm guessing though, and your guess might be better than mine. In any event, the difference will be extremely small.

I think I just took the first table I saw, and ran with it, which was non-corp. Your guess is as good as mine. I'd be happy to swap that to non-corp, but you're right, the difference is incredibly minor, and likely wouldn't be material in the evaluation of one weapon type versus another.

There's also the added complexity of HEIGHT factors as a result of recent testing which MAY be material in the accuracy numbers.. so those random hit values may be subjected to a relative height evaluation as well. There's just a ton of potential variables that we don't FULLY understand which COULD have an impact.

If you think it would be better, can easilly swap to non-corp numbers. Those are all reference data points which can be easilly manipulated freely.



I'm really surprised and disappointed there isn't something about the legendary bow in all that text.

I was tempted. But I just wanted to get it released to the public for feedback.




Have Mark (Riltus) validate the equations are producing anticipated results!



I've been exchanging a ton of PMs with Mark to validate some of my assumptions, and I have some relatively minor updates to make on the slashing, cold, and plasma critical tables. Mark also provided the DF for 518's vacuum bolt. I'll probably be releasing a minor update tonight with some notes about exacly what changed. (spoiler, not much)

Equations appear to be functioning as anticipated, so now it's just validating and gathering more reference data to populate into the reference sheets. For example, I don't think we know the critical outcome breakdowns of many weapon types! Once the community figures that stuff out, its easy to populate!

[Fallen]

Impressive work.

[lordsmo]

Since you asked for input in your first post, here are some additional ideas to improve upon the already high level of awesomeness:

Add a field for "target hitpoints", and use it to calculate crit-kill inclusive expected values for "time to kill" and "mana to kill".
Among other applications, this will help people (especially those who may be forced into a bolting build soon, such as you and I) examine the trade-off in time and mana-efficiency for various bolt spells, including under what circumstances, if any, the higher bolts may achieve greater efficiency than the lower ones due to higher crit-kill probability. For example, we know that 2.5 casts of 904 does more damage than one cast of 910. But there's plenty of endroll ranges where one cast of 910 is more likely to kill than 2.5 casts of 904. Quantifying the mana saved by these crit-kills might be useful...

Add output for "stun probability", "knockdown probability", and "expected value of stun duration".
The main application of this will be for people using the sheet in reverse to compare different armors, enchants, padding, etc...

Add "redux" field for the target.
Not too many critters have redux, I know... probably just greater earth elementals and maybe some grimswarm. Like the previous suggestion, this will be more applicable to people comparing armor for themselves than weapons for critters.

[Whirlin]

Since you asked for input in your first post, here are some additional ideas to improve upon the already high level of awesomeness:

Add a field for "target hitpoints", and use it to calculate crit-kill inclusive expected values for "time to kill" and "mana to kill".
Among other applications, this will help people (especially those who may be forced into a bolting build soon, such as you and I) examine the trade-off in time and mana-efficiency for various bolt spells, including under what circumstances, if any, the higher bolts may achieve greater efficiency than the lower ones due to higher crit-kill probability. For example, we know that 2.5 casts of 904 does more damage than one cast of 910. But there's plenty of endroll ranges where one cast of 910 is more likely to kill than 2.5 casts of 904. Quantifying the mana saved by these crit-kills might be useful...

I dunno... the problem is that you're taking an aggregated average, and trying to leverage that outcome against predetermined thresholds... I think that it may result in putting too much emphasis on the average outcomes that are being evaluated, and not enough emphasis on the final raw numbers. So, evaluating two potential spells may not result in X-1 hits when there's no crit death, but it does improve the likelihood of a death in X swings... Does that make sense?



Add output for "stun probability", "knockdown probability", and "expected value of stun duration".
The main application of this will be for people using the sheet in reverse to compare different armors, enchants, padding, etc...

Working with the that volume of critical tables sucks as is... but this is something I can do if I'm feeling zealous. And each of those basically requires it's own table, with every body part, with every value.



Add "redux" field for the target.
Not too many critters have redux, I know... probably just greater earth elementals and maybe some grimswarm. Like the previous suggestion, this will be more applicable to people comparing armor for themselves than weapons for critters.
[/quote]
I need evidence on this one... I know that some critters, like boars, have innate padding, but I'm not familiar with them having flat out DFRedux... provide a couple swings of data!

[Buckwheet]

Could use some simple tool tips to tell folks what they should be doing to enter in the data. For example: Phantom Crit Weighting, if I don't know what that is or how to calculate it off the top of my head, I would prefer to not go look outside the sheet.

[Whirlin]

Could use some simple tool tips to tell folks what they should be doing to enter in the data. For example: Phantom Crit Weighting, if I don't know what that is or how to calculate it off the top of my head, I would prefer to not go look outside the sheet.
I'll add in some comments fields.

[Riltus]

IIRC greater earth elementals have 30% DFredux (old model).

Total damage = (0.7 * weapon DF * (ER -100)) + crit damage

Mark

[Whirlin]

If it's oldstyle redux, and doesn't impact critical tier, I could always just recognize it as a flat % reduction off of the total blood loss per hit average pretty easilly...

[phantasm]

Is it just me or is the filedropper link broken.

[Whirlin]

Just updated it! May update it again... that hosting solution sucks... maybe I'll just replace it

[time4fun]

Just updated it! May update it again... that hosting solution sucks... maybe I'll just replace it

Yeah can't seem to get into this file.

[Yulis]

Just updated it! May update it again... that hosting solution sucks... maybe I'll just replace it

Zip the file and add it as an attachment to your original post as well. That's what I did with the elemental lore sheet: 1 zip file attached to the topic and also a link to the host site (since it only seems to last for so long before they dump the file on their end).

[Whirlin]

Added version 3 to original post. Now includes the option to CHANNEL your bolt spells to adjust to the new Bolt Channel Critical tables and flat rate accuracy values

[BigWorm]

Cool doc, thanks for creating.

Regarding Google Docs/Sheets, I don't know how to do conditional dropdowns, but you can right click and choose "Conditional Formatting..." now to get a bunch of conditional formatting options in Google Docs.

[Whirlin]

Yeah, but I also utilized named ranges, conditional dropdowns, and some wonderfully advanced Excel stuff that google docs can't handle.

[Whirlin]

Seen a lot of discussion in LNET recently about whether to use this or that bolt against these critters... just a reminder that this exists to handle any necessary comparison for AS/DS resolved attacks!

I pushed a minor update based on new research on the left eye for Impact Critical tables.
They are fatal for T7 and T9, but not T8.