RE: EXCEL Based 2D10 CRT Calculator.

Matrix Games Forums

Forums Register Login Photo Gallery Member List Search Calendars FAQ

My Profile Inbox Address Book My Subscription My Forums Log Out

RE: EXCEL Based 2D10 CRT Calculator.

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> [New Releases from Matrix Games] >> World in Flames >> The War Room >> RE: EXCEL Based 2D10 CRT Calculator.

Page: << < prev 1 [2]

Message

<< Older Topic Newer Topic >>

RE: EXCEL Based 2D10 CRT Calculator. - 4/12/2019 7:44:27 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

2d10 Excel Calculator v005

Note: updated spreadsheet provided in next post.

This update cleaned up some things, including the ability to enter 0 defenders for those cases where the attacker is invading against notionals only.

On the top line the user inputs into two cells ("Description Here" and (A:D)). This will put a description in the blue line above both the Assault and Blitz Calculator tables.

Input. Assault and Blitz tables.
1. The users enters the # of attackers, # of defenders and the # of attackers that are invading and would be loss if the attack fails. Note that the # of invaders is a subset of the # of attackers. In the example show, 6 units are attacking, or which 3 of these 6 units are invading.

2. The user then enters either 0 or 1 the "Extra Loss" flag. 1=extra loss might be called for (e.g., the dreaded 14 on the assault).

3. "Brkthrough" => breakthrough possible flag and is not applicable to an assault.

4. Finally the user enters the odds. Note that this spreadsheet assume fractional odds are in play but will work if not. Just enter the integer odds.

Output.

1. PWIN = chance that the attack will succeed. That is, 1 or more attackers will survive, all defenders will be eliminate, shattered and/or forced to retreat and the attacker(s) will be able to advance into the hex attacked.

2a. Def Shat/Surv (assault) => the chance that 1 or more defenders will survive and that they will be shattered (or forced to retreat at your option).

2b. Breakthrough. Chance that the attacker will achieve a breakthrough.

3. Att 3 Loss. Chance that the attacker will lose 3 units.

4. Att No Loss. Chance that the attacker will not take any losses.

5. Att Ex Lost. The expected number of units that the attacker will lose.

6. Att Org. Chance that all attacking units will remain organized.

7. Att 1/2 Org. Chance that 1/2, rounded up, of surviving attacking units will remain organized.

8. Att Disorg. Chance that all surviving attacking units are disorganized.

9. Att Surv. Chance that 1 or more of the attacking units will survive.

Attachment (1)

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 31

RE: EXCEL Based 2D10 CRT Calculator. - 4/12/2019 7:46:06 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

v005 of the 2D10 CRT Calculator (EXCEL spreadsheet, no macros).

Attachment (1)

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 32

RE: EXCEL Based 2D10 CRT Calculator. - 4/10/2020 10:13:56 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

2D10 CRT Excel based Calculator. version 006.

User inputs.

Top row.
(1) LC description = short descriptive text.

Assault Section.
(1) # Attackers = number of attacking ground units.
(2) # Defenders = number of defending ground units.
(3) # Invaders = the number of attackers that are invading either by amphibious or airborne landing and would be lost if the assault fails. Not that the # of invaders is a subset of the number of attacking ground units.
(4) Extra Loss = 0, 1 flag as to whether or not the attacker might have to take an extra loss (1=yes).
(5) N/A.
(6) Odds = final assault odds.

Blitz Section.
(1) # Attackers = number of attacking ground units.
(2) # Defenders = number of defending ground units.
(3) # Invaders = the number of attackers that are invading either by amphibious or airborne landing and would be lost if the blitz fails. Not that the # of invaders is a subset of the number of attacking ground units.
(4) Extra Loss = 0, 1 flag as to whether or not the attacker might have to take an extra loss (1=yes).
(5) Brkthrough = 0, 1 flag as to whether or not a successful outcome would produce a breakthrough (1=yes).
(6) Odds = final blitz odds.

Rows 1-3, columns H-W.
Captures key data from both the assault and blitz sections which I copy into another spreadsheet to keep track of land combat statistics.

Attachment (1)

< Message edited by rkr1958 -- 7/29/2020 3:55:39 PM >

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 33

RE: EXCEL Based 2D10 CRT Calculator. - 4/10/2020 10:15:29 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

v006 of the 2D10 CRT Calculator (EXCEL spreadsheet, no macros).

Attachment (1)

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 34

RE: EXCEL Based 2D10 CRT Calculator. - 7/29/2020 4:17:38 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

2D10 CRT Excel based Calculator. version 007. Release (1/4).

To support conversion and comparison of my 2D10 CRT Excel based calculator to python (utility & controller) I made the following updates.

Change Log.

1. Streamlined the interface (i.e., 2D10_CRT worksheet), which now includes separate user inputted target description entries for both Assault and Blitz.

2. Added a "Def Can Rtr" flag (0=No, 1=Yes), which if the defense is incapable of retreating, often increases the expected number of defending units killed.

3. Change how "Ex Att Disorg" (expected number of attacking units flipped) is calculated from an approximation to an exact method.

User inputs.

Assault Section.
(1) Tgt Description (Assault) = short description of the assault.
(2) # Attackers = number of attacking ground units.
(3) # Defenders = number of defending ground units.
(4) # Invaders = the number of attackers that are invading either by amphibious or airborne landing and would be lost if the assault fails. Not that the # of invaders is a subset of the number of attacking ground units.
(5) Def Can Rtr = 0, 1 flag as to whether the defending unit(s) can safely retreat. If not (0=No) then all defending units are destroyed by any shatter result.
(6) Extra Loss = 0, 1 flag as to whether or not the attacker might have to take an extra loss (1=yes).
(7) N/A.
(8) Odds = final assault odds.

Blitz Section.
(1) Target Description (Blitz) = short description of the blitz.
(2) # Attackers = number of attacking ground units.
(3) # Defenders = number of defending ground units.
(4) # Invaders = the number of attackers that are invading either by amphibious or airborne landing and would be lost if the blitz fails. Not that the # of invaders is a subset of the number of attacking ground units.
(5) Def Can Rtr = 0, 1 flag as to whether the defending unit(s) can safely retreat. If not (0=No) then all defending units are destroyed by any retreat or blitz result.
(6) Extra Loss = 0, 1 flag as to whether or not the attacker might have to take an extra loss (1=yes).
(7) BreakThru = 0, 1 flag as to whether or not a successful outcome would produce a breakthrough (1=yes).
(8) Odds = final blitz odds.

Attachment (1)

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 35

RE: EXCEL Based 2D10 CRT Calculator. - 7/29/2020 4:30:11 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

2D10 CRT Excel based Calculator. version 007. Release (2/4).

v7 includes a report worksheet, which is broken up into two sections. The top section captures the key statistics from the assault and blitz sections of the 2D10_CRT worksheet. The cells in this section are locked and are not editable by the user.

The bottom section is a report template and its cells are user editable. The idea behind this report section is for the user to copy and paste, as text, lines from the upper section to this one. I've included three "examples" of how it might be used.

In the first example the attacker has "Initial Odds" of 9.35 on the assault. They then consider adding an airborne, followed by +1.5 in HQ support and then maximum ground support. The final odds with all this are 13.347 A, which gives a PWIN of 0.867. However, the attacker has a 13.3% (i.e.,1-0.867) of not taking the hex and thus losing their airborne unit. This gives the attacker pause and he decides to look at a land combat without the airborne drop, which gives him 12.856 A or a PWIN of 0.841. Being only 2.6% less without the drop and not having to risk his airborne corps the attacker decides to go with these odds.

Examples 2 and 3 show the difference between the defender being able to retreat and not being able to do so for an assault and a blitz land combat, respectively.

I believe the definition of all column headers should be apparent with the possible exception of "S/B" (column k). For a blitz S/B is the probability of achieving a breakthrough. For an assault S/B is the chance that 1 or more defending units survive and are shattered.

Attachment (1)

< Message edited by rkr1958 -- 7/29/2020 4:42:04 PM >

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 36

RE: EXCEL Based 2D10 CRT Calculator. - 7/29/2020 4:35:59 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

2D10 CRT Excel based Calculator. version 007. Release (3/4).

PDFs worksheet: The worksheet gives the probabilities that a given number of units are lost, survive, flipped (attackers only) and left organized for both attackers and defenders for both the assault and blitz attacks specified in the 2D10_CRT worksheet. The average and standard deviation of these pdfs are also provided.

Attachment (1)

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 37

RE: EXCEL Based 2D10 CRT Calculator. - 7/29/2020 4:37:48 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

2D10 CRT Excel based Calculator. version 007. Release (4/4).

v7 of 2D10 CRT Excel based Calculator attached (no macros).

Attachment (1)

< Message edited by rkr1958 -- 7/29/2020 4:38:29 PM >

_____________________________

Ronnie

(in reply to rkr1958)

Post #: 38

RE: EXCEL Based 2D10 CRT Calculator. - 12/13/2020 1:55:43 AM

Grotius

Posts: 5798
Joined: 10/18/2002
From: The Imperial Palace.
Status: offline

I just downloaded the latest version, and I'm finding it very helpful. I'm playing a solitaire game myself -- of the Collectors Edition boardgame, on my gaming table. I'm not sure whether RAW v 8.0 uses the same 2D10 table as MWIF and RAW 7, but I imagine they're similar. Does anyone know if the 2D10 table has changed? Ronnie's spreadsheet seems to work well with RAW 8 as far as I can tell.

_____________________________

(in reply to rkr1958)

Post #: 39

RE: EXCEL Based 2D10 CRT Calculator. - 12/14/2020 5:49:21 PM

rkr1958

Posts: 23483
Joined: 5/21/2009
Status: offline

quote:

ORIGINAL: Grotius

I just downloaded the latest version, and I'm finding it very helpful. I'm playing a solitaire game myself -- of the Collectors Edition boardgame, on my gaming table. I'm not sure whether RAW v 8.0 uses the same 2D10 table as MWIF and RAW 7, but I imagine they're similar. Does anyone know if the 2D10 table has changed? Ronnie's spreadsheet seems to work well with RAW 8 as far as I can tell.

My understanding is that there's a slight change to some 2D10 modifiers (e.g., city) but the tables that you roll against are the same. So, the excel calculator is valid for both WiF7 & 8. In fact, I've used it for the vassal CE, which is 8, as well as MWiF, which is 7.

_____________________________

Ronnie

(in reply to Grotius)

Post #: 40