Thursday 15 November 2007

Excel and Wargaming

I currently use EXCEL spreadsheet in my professional activities (teaching and research) but I am also using it as a very useful Wargaming tool. There are some EXCEL utilities for Napoleon's Battles developped by Luis Nunez (¿Nuñez?) that can be downloaded free from the CSGA page (Enter the site and follow the Links for Napoleonics > NB Tools from CSGA). The utilities are known as the Nunez Tables and allow to easily find the statistics for units (NB Roster) and Generals (General). Like all spreadheets, the Nunez Tables can be easily adapted and modified at will.
Another very useful EXCEL possibility is rolling the dice when planning a Scenario in which the OOB, arrival times, entry points and such, are randomised. The solution lays in the EXCEL function RANDBETWEEN(N1;N2) [ALEATORIO.ENTRE(N1;N2) for Spanish language users] where N1 and N2 are, respectively, the lowest and highest numbers you want to be returned (for example 1 and 6 for 1D6). The function allows the use of strange or "impossible" dice, such as 1D5 or 1D13, and can be also used instead a coin (N1=1 and N2=2).

Warning: Previously the 'Analysis ToolPak' must be enabled:
1. From the 'Tools' menu, select 'Add-Ins'
2. Tick the box corresponding to 'Analysis ToolPak'
3. Select 'OK'

An interesting internet article about this topics can be found in the
ABC~All 'Bout Computers site.

RANDBETWEEN(N1;N2) generates a rectangular distribution in which each allowed number has the same probability of appearance. Triangular distributions (like the one found with 2D6) can be obtained by adding up the results of two or more functions. The figures below show the results after throwing 1,434 dice rolls with 1D6, i.e. RANDBETWEEN(1;6) or with 2D6, RANDBETWEEN(1;6) + RANDBETWEEN(1;6).
The above graphs were generated in one pair of minutes and, evidently, do not precised rolling 4,302 times a real six-faces die!. Note also that the figures are not perfect because 1,434 is not a number sufficient great.

I'll use this possibility in my refight of the Wagon Train Table op Teaser in the Siege of Danzig.

2 comments:

  1. Hi Rafa,

    Thats a useful tip, thanks for sharing it. I can see many uses for that in wargames.

    Enjoyed the pictures of the last refight on your main site, excellent.

    Graet blogging!

    Lee.

    ReplyDelete
  2. Lee
    Thaks for your comments. We now can roll a 1D3 die!
    Rafa

    ReplyDelete