Do-it-yourself weapon comparison and ranking (upd Nov22 '04)

[Coprolith]

Hi all

Inspired by Yiktiki's excellent Beastlord spreadsheet i've been using a spreadsheet myself now for some time to calculate weapon dps and generate a weapon ranking to help me decide with weapon upgrades.

I felt it was time for an update, because the old calculations using modal damage simply weren't sufficient anymore in today's EQ, and so many weapons have procs now (augmented or not) that you cant simply ignore them. So I've used the results of my recent ATK and proc rate parses to make the calculations better.

I've cleaned it up a bit and added notes to certain cells so i hope it'll be relatively easy to use. Link to the latest version of the Excel spreadsheet is here:

dpscalc.zip.

Its in ZIPped format. If you don't have Excel, there's a link in Yikitiki's beastlord spreadsheet post in the Library forum.

Features:
1) Uses your ATK rating to calculate average damage per hit. Should be accurate in the ~900-1900 ATK range
2) Incorporates the effects of many AA skills that affect weapon dps
3) Also calculates proc dps (for DD and lifetap procs only so far), based on your Dexterity, and uses an equivalent proc damage for weapons for pet procs.
4) Incorporates elemental damage (but not bane damage)
5) pc- and moblevel hitratios and proc resist rates

What it doesn't do:
1) Give the complete list of all BST-usable weapons. It's a lot of work putting in all the details (i had to add a few more then given in Yiktiki's sheet, so it's more then a cut-and-paste job). Frankly, i could use y'alls help with this. If you see a weapon missing from the DB and know all the details (the ones in the grey fields), pm them to me and i'll add em

2) Give exactly the same dps numbers as your parser does. There are several reasons for this:
- Calculations are based on ideal conditions, that is 100% uptime. In reality combat casting, getting stunned, out-of-range etc will lower your dps. This effect gets worse as the base delay of the weapon gets faster. Also, calculations remove the statistical variance which can be quite large in short parses.
- No riposte damage. Riposte damage increases with the base damage of the MH weapon. Effect is very small tho and won't change the ranking significantly
- Your parser may not be giving you the correct dps number either. Determining the exact fight-time isnt as easy as it looks programmatically.
As a result some parsers underestimate dps a bit
- The accuracy of the calculations is best for characters lvl 60-70 but  decreases if your toon is of lower level. Toons below lvl 60 are on a damage table that leads to noticeable lower dps and this is not taken into account. If its just a weapon comparison you're after this isnt too big an issue. The dps number themselves may not be very meaningful anymore, but the ranking would be affected in only a few places. (Note: this has considerably improved since the introduction of the level-dependent version)

How to use
On the Stats'n skills-sheet you can enter your level, ATK rating, haste%, dexterity and AA skills. I think most of it is self-explanatory, and i've added notes to the input cells to help out. The database sheets should automatically recalculate using the new values (unless you have this disabled in excel's options. If you have, you'll prolly also know how to manually recalculate)

If your weapon isnt in the database yet, there are also fields on the Stats 'n skills sheet where you can enter a weapon setup. You'll have to enter the weapon details yourself.

If you have any problems using the spreadsheet don't hesitate to ask.
Comments/questions/criticisms are always welcomed

/hugs

[Kylaz]

Nice job, as alwaws, Coprolith.

A few questions:

1) How do you compute your haste level ?
Supposing you have a +I% item and a +S% spell, do you do:

Haste=I+S %
or
Haste = 100*((1+I/100)*(1+S/100)-1 )

For example: 40% epic claw and 50% spell haste, is that
haste=40+50 = 90%
or haste= (1.4*1.5-1)*100=110% capped to 100%

2) What exactly is the "I am tanking mobs approx" cell ?
It seems that when I increase that, DPS goes down.

3) Didn't find epic in database, perhaps thats an old thing for you, but I'm still using it in secondary...

[Coprolith]

1) haste = I + S

2) Correct, forgot to mention that one in the original post. Since the weapon ranking now depends on both melee and proc dps, i needed to know both terms as best as possible, a scaling number doesnt do anymore. Tanking mobs lowers your hit ratio because of the mob's dodges, ripostes and parries and therefore lowers the melee dps, but not the proc dps. If you know what % of your time you are tanking that effect can be taken into account. It doesnt have to be exact, just use your best guess. For instance you could use: never tanking = 0, sometimes tanking = 25, tanking regularly = 50, often tanking = 75, always tanking = 100). It's not like the entire table gets turned upside down if you're off by 25%. If you don't know your percentage, entering 50 is your safest bet

3) Yep that is an obvious omission since many BSTs do use their OH epic.
There's a problem with the epic and a select few other such as primal velium that i havent incorporated. I use one haste and one ATK (well 2, HtH ATK and 1HB ATK) value for the tables. But the OH epic has its own haste and adds to your ATK as well. Primal velium adds lotsa ATK, which you'll definitely notice in your dps. So i didnt put them into the table.
There are also weapons that proc an AC debuff, and many weapons add some STR which may or may not increase ATK as well if your not capped yet. This is a level of detail that i havent put into the spreadsheet on purpose. It'll all get too complicated, and with the exception of a select few weapons such as OH epic and primal velium the effects arent big enough to warrant such a huge undertaking.

It's easy enough to calculate it on the Stats'n skills page tho.
Just make sure that when you are entering the OH epic to update the haste and ATK fields as well (and reset them when calculating a combo without the epic)

/hugs

[Scalewulf]

You are the Man, Coprolith!

Would you consider integrating SCF3 in the sheet?  I don't know how much more work it would be and if you don't have the time, I completely understand.  

I see quite a few crits, especially having an aug on the WLK and offhand epic (3 chances to proc, although diminishing).  

Once again, kudos to you and the research you do!

:wink:

[Lewzephyr]

very nice work.... just an FYI found a missing weapons

Rujarkian Stone Knuckles
Dmg 12, Dly 19.
str: +10 sta: +5 cha: +8
pr: +8
hp: +30
Effect:Rujarkian Mist (Combat)

thanks for your hard work.

[Hrann]

Excellent job Coprolith, this is a great resource.

FYI, I just calculated the best possible setup that a non-raider can get (i.e. me).  SHO with 150pt proc and 2 dam aug from Takish would be the third best weapon in the game.  Golden Wraps of the Compact with a 125pt proc added would be the 14th best offhand weapon.  Combined, this combo would be better than an REBB (3rd best 2hb).

Not too shabby at all.  The main limitation is the offhand - a raider with access to an offhand that is of similar quality to their mainhand can do a good bit better, but still, it makes me feel good to think that at least my weapons will be close in quality to some of the best stuff.

Maybe the new expansion will have a good offhand weapon for me ;)

[Yiktiki]

Nice work Cop.  I'm in the process of rebuilding my spreadsheet to allow augs... would you like to integrate that into the main sheet? it would then take some collaboration to keep stuff sync'd up, but it would be nice to have it all in one place.  As it is, we want to work on ease of use with yours... I can't cut n paste so easily, nor can I sort.  I'll gladly help you out with any Excel help you need though.  Let me know if you're interested.

[Hrann]

Sorting works fine for me, but I agree it would be nice to be able to cut and paste more easily.

Both you guys are great for creating these tools.

[Yiktiki]

If I try to sort by MH eff, or total DPS MH it screws the table all up.  In addition to that, I had to go and make 1 row with the name for each column, when I tried sorting a column it would suck the additional lines down into the sheet.  Here is what I had to do to make it sortable.

dps09c-yik.zip

[Coprolith]

It'd be a great idea to integrate my calculations into your sheet Yiktiki, its always easier to have a single, central resource. Im not sure how i'd add augments to it tho, basically what they do is just change base stats or add an proc. Im pretty good with the calculation stuff but when i start adding features things tend to get messy and i end up with a spreadsheet only i can understand. So i may have to take you up on your offer to help out Yik.

Im glad you guys are bug testing  :D
Exactly what seems to be the problem with sorting the tables? I didnt experience any problems myself. The link you provided to the update doesnt work for me Yik so i can't check.

As for adding more skills, yes, i do plan to add more. SCF should be easy, 3 levels, each one adding a fixed percentage to proc dps. I've figured out how to calculate the effects of CF and Frenzy of Spirit as well. Still thinking on how to implement them tho. For FoS i may have to resort to using VB macros or it could get messy. If it turns out using macros is a better solution then i might as well make a macro that calculates it all in one go. It'll be faster and might make it easier to integrate it into Yiktiki's spreadsheet as well.

Thanks for the bug reports and suggestions. Keep em coming

/hugs

[Yiktiki]

One sec, i'll just fix the link.  The sorting issues I had were a) i had multi-line headers, so I couldn't just click the column header and have it sort ascending/descending, and b) the DMG Bonus column on the 1h sheet was a hard-coded 13 for some, and was = the field above for others.  I sorted and it would get confused if the field above wasn't 13 (it was pointing to the column header and giving a #VALUE# error.  I fixed em both on here.  Also added the offhand epic to the list for dps calculations.

On the note of offhand epic,  since you are manually entering your attack score, the atk from that weapon is already accounted for in figuring dps.  It would behave just like any old h2h weapon at that point.  So I was confused as to what made you hold off in adding it.  That may just be my ignorance in the calculations though.

Oh yah.. and on the subject of macros.  I've yet to put any into the spreadsheet but thats about the only way I can think to add options for augments, so it's probably going to be coming along soon.  I'll let you finish your sheet before we try to figure out the best way to integrate to mine.  Should be pretty seamless once yours debugged I think.

[Coprolith]

b) the DMG Bonus column on the 1h sheet was a hard-coded 13 for some, and was = the field above for others

Ah, i see. This is exactly why its a good thing the sheet is being bug tested. Im lazy  :D
Seriously, i copy cells by dragging their corners. if there's a constant number in a cell Excell tends to turn it into a series, 13, 14, 15 etc in this case, so i use the '= the field above' method instead.

The problem with OH epic is that if you manually adjust the ATK value, all weapons in the database are computed with that adjusted dps value, including the 2HBs. The simplest solution would be to make additional entries for ATK and haste bonuses on the database sheets. But that would mean much more user input is required and i was afraid it would cause more rather then less confusion. For instance, i'd have to add a 6% haste bonus to the OH epics haste bonus field because my base worn haste is 34%. Someone with 29% haste would have to enter 11%, and someone with 41% worn haste would have to enter 0. Or i could solve it by splitting the haste% field into worn haste% and spel haste%, and then calculate the bonus as (epic haste - worn haste) (with a lower limit of 0).

Would like to hear what you all think to be the easiest solution.

/hugs

[Yiktiki]

Ahh.. good point.  Hmm.  On your sheet it could get very cumbersome.  Mayhaps after  the sheets are integrated then we could build and ATK off of the stats and gear that is equipped, so it would only give the different attack score when that weapon is equipped.  I'll give it all some thought and check back.

[Coprolith]

I'm gonna make another change, i think. I'll split the 1H'ed database into 1HB, 1HP and HtH just like in your sheet. It'll make it easier to integrate with the cut-and-paste jobs, and it'll also make the formulas i use better readable (i can remove all those messy '=IF(field="HtH",..,..) functions.

/hugs

[Argach]

Very very useful tool. I was actually thinking how cool it'd be to have something like that and then forgot the line of thought completely. One thing I'd like to see is another weapon info slot on the first page for 2hb with only one line, seems confusing for me to clear the off-hand line empty and put 2hb in the first slot, and it'd be easier to compare dual-wield combination and 2hb which is often handy.

Oh, and it is missing SCF and CF/FoA levels, which would change the melee/proc dps ratios somewhat. Would also be nice if it could handle two different procs in a weapon, since all my three viable 1h weapons have two different DD procs, one "unresistable" (time lapse, war strike, soul leech) and another resistable from LDoN.

Btw, at 100% haste, 305 dex, BF5 and 60% tanking ED and shinai main hand had almost exactly same total dps. And that's without SCF3 calculated in. :shock: