Also is the a way to filter and edit text with a cell ? something like HD with just the number of dies and not how many hit points ? hope that made some kind of sense.
RPG Superstar 2010 Top 16
|
Also is the a way to filter and edit text with a cell ? something like HD with just the number of dies and not how many hit points ? hope that made some kind of sense.
If you are trying to parse out components of a cell value, you can try the LEFT(), MID() or RIGHT() functions, or a combination. e.g.
Cell A1 has text string "3d8+4" in it.
=LEFT(A1, 1) = "3" (as a text value)
=VALUE(LEFT(A1,1) = 3 (as a number value)
=MID(A1, 2, 2) = "d8"
=VALUE(RIGHT(A1,1)) = "4"
Then you could create an array of your dice types (d4, d6, d8 etc.) vs. their average values (2.5, 3.5, 4.5 etc.) which will look like this:
d4 2.5
d6 3.5
d8 4.5
...and use the VLOOKUP function to help calculate the averages. Something like:
Average damage =
= VALUE(LEFT(A1,1)*VLOOKUP(MID(A1, 2, 2), {insert your array here}, 2, FALSE) + VALUE(RIGHT(A1,1))
= 3*4.5+4
=17.5
If the number of die or the static modifier are greater than 10 then you will have to adjust the formulas though (or write embedded if/then statements).
Does that help in any way?
Nice! I'd probably use FIND to find the position of the + or - symbol, to make it easy to split both 2 digit numbers as well as single digit ones:
3d10+4
or
3d8+16
Also is the a way to filter and edit text with a cell ? something like HD with just the number of dies and not how many hit points ? hope that made some kind of sense.If you are trying to parse out components of a cell value, you can try the LEFT(), MID() or RIGHT() functions, or a combination. e.g.
Cell A1 has text string "3d8+4" in it.
=LEFT(A1, 1) = "3" (as a text value)
=VALUE(LEFT(A1,1) = 3 (as a number value)=MID(A1, 2, 2) = "d8"
=VALUE(RIGHT(A1,1)) = "4"
Then you could create an array of your dice types (d4, d6, d8 etc.) vs. their average values (2.5, 3.5, 4.5 etc.) which will look like this:
d4 2.5
d6 3.5
d8 4.5...and use the VLOOKUP function to help calculate the averages. Something like:
Average damage =
= VALUE(LEFT(A1,1)*VLOOKUP(MID(A1, 2, 2), {insert your array here}, 2, FALSE) + VALUE(RIGHT(A1,1))
= 3*4.5+4
=17.5If the number of die or the static modifier are greater than 10 then you will have to adjust the formulas though (or write embedded if/then statements).
Does that help in any way?
It does, I'm still new to excel so this will take me some time to understand.
Replace dice with their average values?
|
Replace dice with their average values?
This is fine if no DR is involved. If there is DR where you need to roll above a certain number then taking the average of the die before subtracting DR is wrong.
Eg. 1d8+1 vs DR 5.
Incorrect (average die): 4.5+1-5=0.5
Correct (only rolls of 5 or better cause damage): [0+0+0+0+1+2+3+4]/8=1.25
Do you guys do freelance work :) ?
Replace dice with their average values?This is fine if no DR is involved. If there is DR where you need to roll above a certain number then taking the average of the die before subtracting DR is wrong.
Eg. 1d8+1 vs DR 5.
Incorrect (average die): 4.5+1-5=0.5
Correct (only rolls of 5 or better cause damage): [0+0+0+0+1+2+3+4]/8=1.25
Ummm...yeah, that's not how DR works.
If you have a greatsword and 18 strength your damage would be 2d6+4.
If you roll two 1s then you deal 6 damage. That total damage checks against DR.
The whole damage of a single attack is applied against DR, not just the weapon damage.*
*Excluding things like energy damage from Flaming, or other things that do not apply against DR.
If you have a starknife which deal 1d4 but have a strength modifier of +10 you will deal 1d4+10 damage. It doesn't matter if the enemy has DR 5 and your starknife alone wouldn't penetrate it, because your starknife with your strength will deal a minimum of 11 damage, which means at least 6 damage is getting through.
Now, if your average damage (weapon + str + other) is less than the enemy's DR then you wont be dealing damage very often and you will have account for DR in calculations. But I don't think the original poster is even that worried about dealing with DR.
Nightdrifter was talking about the case where the DR is greater than the minimum possible damage (so 2d6+4 vs DR10, to take your example).
His point was that the average damage is NOT (2*3.5 + 4) - 10 = 1 (It's actually 1.56).
If the DR is less than the minimum possible damage (so 2d6+4 vs DR5) then the quick calculation works - the average damage is 6 in that case.
|
|
To clarify the example of 1d8+1 vs DR 5:
Roll / Damage (if no DR) / Damage after DR 5
1 / 2 / 0
2 / 3/ 0
3 / 4 / 0
4 / 5 / 0
5 / 6 / 1
6 / 7 / 2
7 / 8 / 3
8 / 9 / 4
The correct method is to just average the numbers in the last column: [0+0+0+0+1+2+3+4]/8=1.25
The discrepancy between the two methods occurs because the correct method is averaging over max{dmg-DR,0) and the incorrect is just averaging over {dmg-DR}.
I think the confusion over what I was saying was from poor wording. "If there is DR where you need to roll above a certain number" is better said as "case where the DR is greater than the minimum possible damage", eg. you need to roll at least a 5 in the above example to do any damage and even then it's reduced.
Oh I see now. Sorry for the misunderstanding.
|
|
If you have python 3 installed (just install it - you don't need to know squat about programming in python) I've made a quick and dirty script to calculate DPR here. (Windows only)
Should be fairly self-explanatory, though I should mention BAB is only used to determine number of iterative attacks.
Also, since it iterates over all possible rolls (to take into account DR as in the above examples) it might get a tad sluggish when using large numbers of dice, eg. 10d6 precision damage.
Leaving fields blank will cause crashes!
It's a work in progress and I'll add more features when I get time. If you want to check the calculations or see the code, just open in any text editor.
Average damage dice without the bonus damage (i.e. 1d12):
Average damage dice with bonus damage (i.e. 1d12+4, requires +0 at the end if there is no bonus damage):
Average damage with or without bonus damage (i.e. 1d12 or 1d12+4):
Important note: This forum's coding is a piece of f&+#ing s#$&. The board forces in a space after every 75 characters. Since I can't find a way to fix that, you have to put the seperate lines into one formula.
@Nightdrifter: The correct result for your DPR if your min damage is below the DR is "really f&@*ing low". Does it really matter if your DPR is 1,56 or 1? You won't be the guy killing that enemy, anyway.
|
|
@Nightdrifter: The correct result for your DPR if your min damage is below the DR is "really f~!$ing low". Does it really matter if your DPR is 1,56 or 1? You won't be the guy killing that enemy, anyway.
Not necessarily. The 1d8+1 vs DR5 example was inspired by the last session I GMed where the PCs fought some creatures with DR 5 and 4 hp. Still possible to bring them down to 0 hp in 1 shot. Edit: should also mention they were undead, so destroyed at 0 hp.
I'm slowly getting my head around all this.
This is what I'm trying to convert.
https://docs.google.com/spreadsheets/d/1_DfpcDZCgt1Jn-NLZswwb6AGbkUmkfBtg8Z KdSomqTo/edit?usp=sharing
|
|
I'm slowly getting my head around all this.
This is what I'm trying to convert.
https://docs.google.com/spreadsheets/d/1_DfpcDZCgt1Jn-NLZswwb6AGbkUmkfBtg8Z KdSomqTo/edit?usp=sharing
Link isn't working for me. Edit: this does
|
|
@Loren: I've switched how the calculations are done in the next version (here), though my todo list of stuff to add means it's still very much a WIP. If you run it please note that the first time you do so it'll download numpy (an extra python module needed for some of the math). This version works the same, but prints some basic info to the terminal.
Instead of slogging through all possibilities I use generating functions (see eg here) in this new version. The coefficients of the polynomials involved are like the (0,1,1,1,1,1,1) you suggest and from reading your post it sounds like we're on the same page (eg. the average damage calculation you suggest is exactly how it's done in the new code already). It's much faster. 8d6 might take a good 20 seconds on my desktop in the old method. This new one allows me to do 100d6 in the blink of an eye. I do worry about the floating point issues, but don't know a way around them yet.
Should add that one annoying quirk of how I parse dice input is that negatives (eg. 1d6-1) aren't handled properly. Fixing that issue is on the todo list.
======================================
From the looks of Lastoutkast's spreadsheet it seems like he wants to take the info from a stat block, so one of my goals is to tweak the input to allow this. Will start a new thread when I've done more work on it to not threadjack this one too much.