updated dps spreadsheet (post Update 1) BETA version

[Gol]

First of all, giving credit where credit is due:
cforce: for his original effort of discovering the attack speed formulas, as well as the updated BAB attack speed modifiers
Absolute Omniscience: for explaining a bunch of stuff to me, specifically the new TWF mechanics

I've simply tried to incorporate their work into an easy-to-use DPS spreadsheet. You don't need to know everything about everything to calculate your DPS. Are you a Kensai 3? Great! Enter "Kensai 3" under fighter enhancement (there's a dropdown box for it so you can't fat-finger it, too).

That said, I'm primarily posting for FEEDBACK. I don't presume this to be 100% accurate. I'd like to think that I have done at least a decent job so far, though. What I'm asking for is that you use it, review my formulas, look for things I'm missing, etc, and let me know how it should change. I'm particulary interested in math errors and damage omissions. There should be a way to enter just about any type of damage into the spreadsheet.

Feel free to Post or PM with feedback. I'll post here with revisions & notes when I make changes to it. I'll keep a known issues list running in this thread.

Thanks for reading!

Links:
v0.2 Office 2007 Version
v0.2 Office 2003 Version

v0.1 Office 2007 Version
v0.1 Office 2003 Version
* Note, my server is restricted to 10 concurrent sessions, so please save the file instead of opening. If you get a connection error, just try again.



KNOWN ISSUES:

• THF Twitch attack chain stats are as-of-yet unconfirmed. Please do not use my THF Twitch calculations as real/expected results.
• Glancing Blows is not included, but I'm shooting for v0.3 for that feature.

COMING FEATURES

• Glancing Blows
• Activation timer inclusion for Frenzy

[Ustice]

Excellent work. Any thought to making this as a Google Spreadsheet? The advantage would be that if there is an update, then people wouldn't have to redownload it, nor update their bookmark.

[Gol]

Excellent work. Any thought to making this as a Google Spreadsheet? The advantage would be that if there is an update, then people wouldn't have to redownload it, nor update their bookmark.

Never used a Google Spreadsheet before, but I'm not opposed to it. Is there a file converter for it? PM me your gtalk handle and we can chat.

[binnsr]

As far as I know, google spreadsheets doesn't support cell validation (i.e. the drop-down boxes) that Gol is using for fighting styles, etc.

Nice work, Gol. I'm sure I'll get some mileage out of it.

One suggestion that I would make is have a hidden sheet with weapons and their base stats. That way you could make a dropdown for weapon type and have all the 'normal' damage numbers fill in. (my character planner (in my sig) has a tab with this information in it - my (abortive) attempt at a basic damage calculator)

[Gol]

One suggestion that I would make is have a hidden sheet with weapons and their base stats. That way you could make a dropdown for weapon type and have all the 'normal' damage numbers fill in. (my character planner (in my sig) has a tab with this information in it - my (abortive) attempt at a basic damage calculator)

One of the things I had problems with while doing this is implementing anything in cross-reference style (ie, this text matches this number). I have some pretty nasty IF statements to do what I needed to do, but I didn't really want to use that method for weapon types. I'll look into it again, though.

[binnsr]

One of the things I had problems with while doing this is implementing anything in cross-reference style (ie, this text matches this number). I have some pretty nasty IF statements to do what I needed to do, but I didn't really want to use that method for weapon types. I'll look into it again, though.

Have you looked at hlookup() and vlookup() ? That's what I've used for that functionality in most of my spreadsheets.

For instance, I have a hidden sheet with all of the classes and saves per level.

Fighter	Fort	Reflex	Will	BAB
1	2	0	0	1
2	3	0	0	2
3	3	1	1	3
4	4	1	1	4
5	4	1	1	5
6	5	2	2	6
7	5	2	2	7
8	6	2	2	8
9	6	3	3	9
10	7	3	3	10
11	7	3	3	11
12	8	4	4	12
13	8	4	4	13
14	9	4	4	14
15	9	5	5	15
16	10	5	5	16
17	10	5	5	17
18	11	6	6	18
19	11	6	6	19
20	12	6	6	20

Then, in my saves table, I do a lookup based on class level
Fortitude: =IF($U7>0,VLOOKUP($U7,Saves!$A$68:$D$87,2),0)
Reflex: =IF($U7>0,VLOOKUP($U7,Saves!$A$68:$D$87,3),0)
Will: =IF($U7>0,VLOOKUP($U7,Saves!$A$68:$D$87,4),0)

So in your case, the hidden sheet would be something like:

Weapon	min	max	crrng	crmlt
Khopesh	1	8	19	3
GreatAxe	1	12	20	3

And to calculate average damage per swing, you'd have
=average(1*vlookup(A1,weapons!A1:A100,2),vlookup(A 1,weapons!A1:A100,2)*vlookup(A1,weapons!A1:A100,3) ))

And of course, if you've already done all of this, nevermind my ramblings

[Gol]

And of course, if you've already done all of this, nevermind my ramblings

I think I got it working since your last post. I'm using the OFFSET function now, which basically does the same thing.
=OFFSET('Weapon Data'!$A$1, MATCH(C5, 'Weapon Data'!$A$2:$A$100,0),1)

I'm probably going to convert a bunch of my other formulas to do similar things for other reference data.

[binnsr]

cool. I recently discovered offset() myself

Am only using it in a few places in my character planner at the moment as I don't really have the personal time to do the major revamp of it that it really needs..

[Hjarki]

Have you looked at hlookup() and vlookup() ? That's what I've used for that functionality in most of my spreadsheets.

For instance, I have a hidden sheet with all of the classes and saves per level.

Saves only have two formulas:
Primary save = 2 + floor(Level / 2)
Secondary save = floor(Level / 3)

BAB simply uses a multiplier:
Bar/Pal/Ftr/Rgr = floor(Level * 1)
Brd/FvS/Clr/Rog/Mnk = floor(Level * 0.75)
Sor/Wiz = floor(Level * 0.5)

So rather than an entire table for each class, all you need is a table with all the classes listing which saves are primary/secondary and what the BAB multiplier is.

[Absolute-Omniscience]

Having quickly glanced it through I notice that it's hard to keep track of the different builds. I'd do it like I have done on mine, using different colours on each. I have, light blue, light yellow, and then light blue again, etc.
The text is very hard to read at the breakdowns as well, using that darkorange on the gray is a bad choice, IMO.

You're Missing following:
Fighter enhancements to damage. I and II.
Monk True Ressurection damage bonus
Divine Sacrifice
Exalted Smites
Madstone yes/no
Winstance IV (+10%)
Elemental Resistance for the mobs
Activation time. (1,2 sec for everything*, 0,6 if build has QD)

*everything =, Frenzy, Death Frenzy, PS, Boosts. Divine Might also has a ~3 sec casting time that can't be lowered. Must be recast each 1 min

Also, how have you done the FB +crit multiplier? The only place I can see so far is the crit multiplier under weapon, but changing that will change the crit multiplier on the whole crit range, whilst the FB one is on 19-20 only.

I would move the Mob immunities (resist, dr, fort) to a seperate headline as well, and move the items to one as well, or combine them with something else.

The value on crits can be lowered lower than 0, if the DR is high enough. Put an if there so that IF(X-DR<0;0;X-DR)

Anyways, that's what I could find now. Good work keep it up. My 1.2 version is soon to hit the streets as well.

[Absolute-Omniscience]

Excellent work. Any thought to making this as a Google Spreadsheet? The advantage would be that if there is an update, then people wouldn't have to redownload it, nor update their bookmark.

I don't know how it is with his calc, but I'd guess it's the same. I did try goggle docs, but I the two main problems were that Dropdowns don't work in it, and a calculator involving this many if's and equations is far too demanding for Goggle Docs to handle. My Calc lagged like hell and took ages to change variables, when I tried Docs.

[toughguyjoe]

Well, Its a cool spreadsheet.

My only feedback is that when I put in the DPS for my Frenzied Berserker III, Ihurt.......

The calculations on his amazingly ridiculous DPS made my system crash.

I'll just chalk that up to how awesome my toon is.

Hope the mind boggling damage calculations didn't hurt the system on your end!

[Gol]

You're Missing following:
(stuff)

I'm going to have to PM you about a couple of these. I'm not sure what some of them do.

[Hydro]

Nevermind.

[Vanshilar]

Some suggestions:

1) As others have mentioned, you should do use a table method for the weapon stats, rather than the person having to fill it out. This simplifies data input a lot.

2) I would recommend putting total DPS above fighting style, and then put it above the freeze panes line. This way as you vary different things you can immediately see how that affects your total DPS, instead of having to scroll up/down.

3) I'm not sure if glancing blows was taken into account. Even on a single target, you still glance on that target.

4) For crit range, you could make it drop-down, "20" "19-20" "18-20" etc. This just changes everywhere you have "C21" to "(21-left(C21,2))", to make it easier to use.

5) Is there a set of raw data that you used to make the calculator? Especially the attack speeds =P

[binnsr]

Saves only have two formulas:
Primary save = 2 + floor(Level / 2)
Secondary save = floor(Level / 3)

BAB simply uses a multiplier:
Bar/Pal/Ftr/Rgr = floor(Level * 1)
Brd/FvS/Clr/Rog/Mnk = floor(Level * 0.75)
Sor/Wiz = floor(Level * 0.5)

So rather than an entire table for each class, all you need is a table with all the classes listing which saves are primary/secondary and what the BAB multiplier is.

That might be simpler in the long run, but I've already got it the way it is and as I mentioned, I really don't have time for the overhaul my sheet needs.

I was mentioning saves as an example Gol could use in relation to creating a table of weapons statistics.

[Absolute-Omniscience]

Especially the attack speeds =P

The attackspeed numbers are from Cforce.

[Gol]

Some suggestions:

1) As others have mentioned, you should do use a table method for the weapon stats, rather than the person having to fill it out. This simplifies data input a lot.

2) I would recommend putting total DPS above fighting style, and then put it above the freeze panes line. This way as you vary different things you can immediately see how that affects your total DPS, instead of having to scroll up/down.

3) I'm not sure if glancing blows was taken into account. Even on a single target, you still glance on that target.

4) For crit range, you could make it drop-down, "20" "19-20" "18-20" etc. This just changes everywhere you have "C21" to "(21-left(C21,2))", to make it easier to use.

5) Is there a set of raw data that you used to make the calculator? Especially the attack speeds =P

1) Already done, will be in the next version.
2) Good idea - I can do that.
3) No, it was not. If somebody provides the math and I'll include it.
4) See #1.
5) I can if it really matters, but it merely went to support cforce's formulas.

[Absolute-Omniscience]

3) No, it was not. If somebody provides the math and I'll include it.

Glancing blows
Base damage:
((BASE damage + <THF feat>*10+IF(Barbcapstone="Yes";10;0))/3)-Enemy DR

So, if it's a barb with +40 damage, and using a greataxe for 10,5 average damage it's 50,5 base, using GTHF.
(50,5+3*10+10)/3=~30 per glance.

Effect proc rate
Each feat: 3%
Barb capstone: 5%
Each Pre Teir: 5%
Each WF enhancement rank: 2%

Total max possible: 9% feat + 5% capstone + 15% fb + 6% wf = 35%, which is about what has been reported.
I haven't tested these numbers to the end of the world, but they seem accurate enough. Besides, even upping it to 100% effect proc rate barely changes the end dps anyways.

Vicious from FB, Kotc, paladin capstone, all proc 100% on glancings.

IF(Fighting style = "THF-Twitch";SUM(base glance dmg + effects + 100% proc rate effects)*0,5*0,95

0,5 = 50% of the blows are glancings with twitch. 0,95% is that 5% of the glancing misses.

IF(Fighting style = "THF";SUM(base glance dmg + effects + 100% proc rate effects)*0,75*0,95

=
IF(Fighting style = "THF-Twitch";SUM(base glance dmg + effects + 100% proc rate effects)*0,5*0,95;IF(Fighting style = "THF";SUM(base glance dmg + effects + 100% proc rate effects)*0,75*0,95))*<Number of targets hit by glancing blows input>

Put up a input box where the uses states how many mobs are being fought.

Then when you have an average damage number from the glancing blows, simply add it to the damage on the 20 hits (meaning after crit multiplication).

I also included the THF feat selection, making my glancing calculations quite a bit longer and more complicated.

[Gol]

Does Smite / Divine Sacrifice have the same activation delay as boosts? For some reason I was thinking that these specifically were no-cost activation.