MS Excel for artillery resolution combat

These weeks I am trying to adapt the ranged combat (artillery) tables from the BRITISH ARMY manual 'KRIEGSSPIEL'
(1815 Draft), increasing the time ratio from 2 minutes to 15 minutes.
By proportionally increasing the losses, and adjusting values, I have managed to create the following table in MS Excel:



1C= 25%; 4C -< 1T; 1 I -> 5/6; 2I -> 4/6; 3I -> 1T
1C equals 25% of the regiment (every regiment 4 squadrons)
4C equals All the cavalry regiment
1 I equals 5/6 substitution piece
2 I equals 4/6 substituion piece
3I equals all the battalion

The table is still in the preparation phase, but with some simple formulas you can "automate" the obtaining of results randomly:

The following example would be for a 6-pounder battery, which would correspond to die 5 in the table, with bad effect, and at
a distance between 400 and 800 meters (500-1000 paces more or less). The result would be the retreat of the objective unit 300 m,
and with a loss of 1C if it were a cavalry regiment and 1I if it were infantry battalion.



I'm working in some other factors as artillery on artillery fire, counterbatterie, etc.

If real life allows me I'll show you some examples next days.

Two fairly easy examples for artillery, flat terrain, units in open terrain or without natural protection. I think I have to refine the topic of artillery vs.cavalry more, since the time turns represent 15', which is a good amount of projectiles fired, but unless one is Lasalle or is in the reserve, the most normal thing would be to rectify the position of the regiment...

The option of discounting the units resulting from a roll of 1D6 is to look for a more "plausible" result. It could also be considered that if the roll is >4, half the losses are counted, rounding down (in our case (3/2 = 1.5 -> 1 squadron) and if it is lower, all losses are applied. This is just an example, of the several that can be applied... What do you think?

The calculations have been counted with the distances in yards.

"Two fairly easy examples for artillery, flat terrain, units in open terrain or without natural protection. I think I have to refine the topic of artillery vs.cavalry more, since the time turns represent 15', which is a good amount of projectiles fired, but unless one is Lasalle or is in the reserve, the most normal thing would be to rectify the position of the regiment..."

I think that's right.  Unless the player has ordered the cavalry to remain in position come-what-may, then I think they would simply move back.  Even in such a case, I wonder how long they would stand?  

Conversely, my perception is that infantry would normally stand and accept the losses, especially if so ordered.

But someone else might have more historical knowledge on this...........in which case you should listen to them, not me haha

Martin J

Hi Martin,

Normally the cavalry did not retreat when it was in reserve, and they held their own. If they occupied a position in the first or second line, sometimes the position was rectified somewhat, out of pure common sense. The infantrymen could still jump against a fast ball by jumping, a more complicated maneuver for horses formed in rows or columns...

There are many different situations, but I think we should distinguish a cavalry attack (a la Balaklava, to say the least) from a bombardment on stationary cavalry...



The three squadrons of the 5th Prussian Hussars under the command of Colonel Wolkswagen charge against the enemy battery of 6 Pdrs.
Result: 1 enemy squadron destroyed and the other two have to retreat 150 m/164 yds.

Well, I have to continue working on the table, and above all the situations...

As the cases are very diverse and, like all tables of this type, you always have to end up making adjustments so that the results are somewhat coherent,
I show you the table with all the formulas, in case anyone dares to make their own. Using these tables automates the calculations and allows us to
obtain the result we want sooner:



In my haste I have not finished translating all the sentences of the formula from Spanish to English, but you only have to replace, if applicable: Y:AND; FALSO:FALSE; SI:IF; BUSCARV;VLOOKUP.

Finally, if you want to automate the obtaining of results, without buttons with macros, what I have done is add a Format control:



=IF($L$11=TRUE;IF(AND($O$7="12 Pdrs";$O$10="Good");..........);"-")

When the control is activated, it calculates the formula and if it is deactivated, the cell shows us "-".