Pyramid #3/33 Bow Worksheet: Still working?

[DouglasCole]

Try these in place of I51 and I52:

=IF(MOD(G51,3.5)<2.5, TRUNC(G51/3.5)&"d+"&TRUNC(MOD(G51,3.5)), TRUNC(G51/3.5)+1&"d-1")

=IF(MOD(G52,3.5)<2.5, TRUNC(G52/3.5)&"d+"&TRUNC(MOD(G52,3.5)), TRUNC(G52/3.5)+1&"d-1")

This won't help with the values less than 1d (yet), but it should help the High Tech/Weapon Master issue.

The formula produced these trial results:

Points of Damage	Dice
15.36	4d+1
11.22	3d+0
0.53	0d+0
9.88	3d-1
3.87	1d+0
2.62	1d-1
20.27	6d-1
3.38	1d-1
13.82	4d-1
20.94	6d-1
6.62	2d-1
11.58	3d+1
20.2	6d-1
11.73	3d+1
18.76	5d+1
9.24	2d+2
3.38	1d-1
16.96	5d-1
19.18	5d+1
15.59	4d+1
14.36	4d+0
20.08	6d-1
18.15	5d+0
19.06	5d+1
1.44	0d+1
8.1	2d+1
5.93	1d+2

[DouglasCole]

Originally Posted by Icelander

Yay!
[and to fill up character limit.]

OK, my next post (already up) has everything from 1d and higher.

I think I may need to resort to a VLOOKUP for results less than 1d, but that at least makes for comprehensible cell values.

Doug

[Icelander]

Excel tells me that the formula for I51 contains an error and won't accept it. I'm sure it's a tiny issue that I should spot immediately. But I don't.

[DouglasCole]

Originally Posted by Icelander

Excel tells me that the formula for I51 contains an error and won't accept it. I'm sure it's a tiny issue that I should spot immediately. But I don't.

Huh. I just tried it in my own version, and it works fine. I did my work in a different spreadsheet, and so I think I just did what you would do: highlight the text starting with the equals sign, to the right, then highlight the entire cell and paste it in.

It even turned 6.99 points of damage into 2d-1 instead of 2d.

I'm on Office 2010; you?

[Icelander]

Originally Posted by DouglasCole

Huh. I just tried it in my own version, and it works fine. I did my work in a different spreadsheet, and so I think I just did what you would do: highlight the text starting with the equals sign, to the right, then highlight the entire cell and paste it in.

It even turned 6.99 points of damage into 2d-1 instead of 2d.

I'm on Office 2010; you?

Same. I don't get it.

For some inexplicable reason, what seems to be bothering Excel is the fact that the divisor is 3.5. I tried changing it to a whole number, which made Excel happy, but didn't actually help me much.

[DouglasCole]

OK, for the damages less than 1d, I'm going to use a kludge, resorting to a VLOOKUP table. This table assumes that the minimum damage you can roll is 1, rather than 0. It also will assign ALL damages less than 1 point as 1d-5 just for the sake of simplicity.

0	1d-5
1	1d-4
1.17	1d-3
2	1d-2
2.67	1d-1

Paste this mini-table into cell H63. That should give you values from H63 to I67, five rows and two columns.

that should allow the following formula to stand in for all values:

=IF(G51<3.5, VLOOKUP(MOD(G51,3.5),$H$63:$I$67,2,TRUE), IF(MOD(G51,3.5)<2.5, TRUNC(G51/3.5)&"d+"&TRUNC(MOD(G51,3.5)), TRUNC(G51/3.5)+1&"d-1"))


EDITED TO ADD:

Icelander had some real issues with text formatting, and he had to play with commas, periods, colons, and semi-colons to make it work. I reproduce his final equations below, even though they're also in a separate post.

=IF(G51<3,5; VLOOKUP(MOD(G51;3,5);$H$63:$I$67;2;TRUE); IF(MOD(G51;3,5)<2,5; TRUNC(G51/3,5)&"d+"&TRUNC(MOD(G51;3,5)); TRUNC(G51/3,5)+1&"d-1"))

=IF(G52<3,5; VLOOKUP(MOD(G52;3,5);$H$63:$I$67;2;TRUE); IF(MOD(G52;3,5)<2,5; TRUNC(G52/3,5)&"d+"&TRUNC(MOD(G52;3,5)); TRUNC(G52/3,5)+1&"d-1"))

[DouglasCole]

Originally Posted by Icelander

Same. I don't get it.

For some inexplicable reason, what seems to be bothering Excel is the fact that the divisor is 3.5. I tried changing it to a whole number, which made Excel happy, but didn't actually help me much.

Urrr. The only thing I can think of here, if the MOD command doesn't like fractional divisors, would be to hard-code it in.

That would turn the previous equation (NOTE: I put in the VLOOKUP for values less than 1d in this one!) into this:

=IF(G51<3.5, VLOOKUP(G51-TRUNC(G51/3.5)*3.5,$H$63:$I$67,2,TRUE), IF(G51-TRUNC(G51/3.5)*3.5<2.5, TRUNC(G51/3.5)&"d+"&TRUNC(G51-TRUNC(G51/3.5)*3.5), TRUNC(G51/3.5)+1&"d-1"))

[DouglasCole]

Originally Posted by Icelander

Same. I don't get it.

For some inexplicable reason, what seems to be bothering Excel is the fact that the divisor is 3.5. I tried changing it to a whole number, which made Excel happy, but didn't actually help me much.

Try this direct replacement instead:

=IF(G51-TRUNC(G51/3.5)*3.5<2.5, TRUNC(G51/3.5)&"d+"&TRUNC(G51-TRUNC(G51/3.5)*3.5), TRUNC(G51/3.5)+1&"d-1")

[ErhnamDJ]

I don't understand what y'all are doing with the Excel stuff, but I have an idea for handling the lower damage ranges.

What I've been doing for damage rolls, to reduce variability, is have players roll 3d6 for each die and compare that to the following chart to get their actual damage:

3d6 Result - Damage Roll
3 - 1
4 - 2
5 - 2
6 - 2
7 - 3
8 - 3
9 - 3
10 - 3
11 - 4
12 - 4
13 - 4
14 - 4
15 - 5
16 - 5
17 - 5
18 - 6

It seems to me that you could easily create a new such chart for handling those low damage rolls. Sure, it requires some extra rolling and consulting, but it would get the job done. Figure the average damage, then put that into a chart like that. How else do you figure a half point of damage? Or a quarter point?

[Icelander]

Originally Posted by DouglasCole

Try this direct replacement instead:

=IF(G51-TRUNC(G51/3.5)*3.5<2.5, TRUNC(G51/3.5)&"d+"&TRUNC(G51-TRUNC(G51/3.5)*3.5), TRUNC(G51/3.5)+1&"d-1")

Nothing works! Even when I manage to get Excel to stop being angry about fractional divisors, it just picks out something else in the formula to whine about.