Monday, December 7, 2009

Easy Programming

Ah, this is Canada, and the temperature is -26 degrees celsius.  For the gentle reader who is American, that is -15 degrees fahrenheit.

So as I won't be going out anyway, I shall try to elaborate upon my pathetically simple program for rolling combat results.  This will require a basic skill in excel, but if you fill in the cells as I show you, the system can be yours.

Starting with cells A1 through J1, you'll want to copy the following headings: Attacker, No. of Targets, THACO, Target AC, To Hit Roll, Effect, Damage Dice, Modifier, Damage, Target Chosen.  And now I shall explain what each of those means.

Attacker.  This cell identifies the attacker.  Skipping a line, in cell A3, you want to write the formula (and for the purpose of this post, all formulas will be written in red, inside black brackets), (=1+A2).  Since cell A2 is empty, this will produce the number 1.  If the cell is copied and pasted into A4, it will give you the number 2.  It is simply a counter, so you will know which creature did the hitting.  You need to mentally designate the attackers in a group from number 1 to number whatever, which I usually do by counting left to right, or top to bottom, depending on the orientation of the attackers.  Obviously, this needs to be done before addressing the excel chart.

No. of Targets.  In hand-to-hand, this would usually be one target, the one in front of them, unless the attacker has multiple attacks.  The purpose of this system, however, is to manage the low level scum, so for the most part, they will have only one attack.  However, if you want, you can decide to have the die select up to however many targets you wish - if you want to make it more complicated.  Usually, in hand to hand, most creatures will be adjacent to either 2 or 3 opponents ... so you could make the number of targets "2", meaning left or right, or "3", meaning left, right, or center.  If you choose "3" for melee combat, and you get a result of the third man where only two exist, you can simply decide that it counts back to the first man again.

I know that's confusing.  It will be less confusing once you've read the whole post.  I suggest you come back.

If the attacker is firing a missile weapon, the number of targets increases considerably ... potentially the whole field of fire.  The main difficulty created is that different ACs will be attacked at once, but that isn't important to this column.  Again, mentally assign a number to every creature in the line of fire, count them all and then imput the number into the cell.  For the purpose of my demonstration below, I will put (6) into cell B3.

THACO.  Obviously, the number the attacker needs to hit AC zero.  This isn't complicated, the number is available in the DMG.  Remember, this is the attacker's number, not the defender's.  For my demonstration, I'll assume I have men-at-arms attacking, so I'll write (20) into cell C3.

Target AC.  As I said, this could be complicated, if there are multiple armor classes being attacked.  For ease, I suggest that you use the lowest AC of all those being attacked, and then adjust as necessary.  Once again, this is merely a number you need to put in.  I'll assume the least equipped opponent is leather and shield, and write (7) into cell D3.

To Hit Roll.  This is the d20 roll that the individual 'throws.'  It is a simple formula: (=rand()*20+.5), which you input into cell E3.  The numeral 20 defines the random number to be thrown; the computer does not designate whole numbers, but creates any random number (with decimals) between zero and 20 ... thus it is possible to get a result of 0.000356.  Thus, you'll want to reduce the number of decimals to none, showing only the rounded off number.  The 0.5 is added at the end of the formula is so that you won't get a result less than '1' shown.

Effect.  This is simply the formula to tell you if the attacker hit or missed the target.  For those familiar with if statements, it's again a very common formula (=IF(E3>=C3-D3-0.5,"hit","miss")) ... otherwise, that might look weird.  Note that you only want 1 bracket at the end, there, the one shown in red.  This is put into cell F3.  All it does is to remove the target AC from the THACO, to give excel the number you need to hit; this is then compared with the To Hit Roll, and if the To Hit Roll is equal to or higher than the number needed to hit, you hit.  Then it prints either "hit" or "miss" in the cell.

For you geeks, note that the modifier added to the To Hit Roll is compensated for here ... if you've noticed the anomaly, you should be able to figure out why it is there.  It is simply because a "12.7" would appear in the To Hit column as a "13" ... if I were to have the formula subtract 7 from 20, then a 12.7 would show a miss.  Everything above 12.5 should count as a hit.  Get it?

Damage Dice.  So now we know if the target has hit or missed.  In cell G3, you want to record the highest number on the die in question ... thus a six-sided would be a (6).  An eight-sided would be an 8 and so on.  Thankfully, there will be very few situations where more than one die is involved.  I suggest you roll those situations separately, and not use excel.  It would be easier, in many cases, to just roll dice.

Modifier.  This is in case the weapon being used is a mace (2-7), or all the creatures have a strength bonus.  Simply input the number that you want added to the damage die.  Since these are zero-level humans, I will input (0) into cell H1.

Damage.  Simply the damage that is done.  The formula reads thus: (=IF(F3="hit",RAND()*G3+0.5, "")) ... again, only one bracket there at the end.  Input this into cell I3.  This simply says, if there is a hit, the random number is rolled for damage and shown here.  Again, that pesky 0.5 is needed to give a number between 0.5 and 6.5, which is rounded off (display option) to show a number 1 to 6 in the cell.  If the shot was a miss, the cell will appear empty.

Target Chosen.  This indicates who the damage was done to.  The formula reads (=IF(I3="","",RAND()*B3+0.5)) ... still, just one bracket at the end.  Input this into cell J3.  This identifies which target was struck, randomly chosen from the number of targets you designated in column B.  If the attacker missed, no result will appear.

If you've done this correctly, you need only copy each line to the line below to produce another 'attacker'.  You can, in effect, produce hundreds of attackers, at the click of a button.  The problem with excel, however, is that every time you click, the numbers will recalculate and the results will change ... UNLESS you dig around into the formulas page and designate the calculations to occur manually.

I don't care to do that, however ... it takes time and there's an easier way.  If you highlight everything you need, and then open another sheet or page in excel, you can 'paste special' everything you've done as 'values only' ... which will get rid of the formulas on the new page (keeping the old page untouched) and then you can sort out the numbers however you wish.  Two hundred bowmen?  No problem.  Make 200 lines and then sort according to what targets got hit.  Tickety-boo, apply the damage.

Incidentally, I'm thinking about designating one of the party as a record keeper, to help me keep track of damage to the enemy ... should speed up some things.

Using the tables, I produced the following results for 20 bowmen firing at 6 targets, all AC7.  Make of it what you will.


R said...

I use the following excel formula to roll a d20


Saves time by not having to edit the cell formats.

You could take it a step further and use Vlookup to keep track of each attackers and defenders hit points and then have excel "kill" them when their hit points reach zero (i.e., multiply their damage and Attacker Rank # by 0 so they're removed from effecting anyone else).

skoormit said...

Excel 2007 has a Randbetween(low, high) function. Just pass it the inclusive endpoints and it gives you a random integer between the two. Rolling a d20 is simple: =RANDBETWEEN(1,20)