EXCEL Based 2D10 CRT Calculator.

[rkr1958]

Hey guys, just thought I'd share with you status of a 2D10 CRT calculator that I'm working on in EXCEL, which is still in the "development" and checkout stages. There’s actually two calculators, one for the assault and one for the blitz table. Player inputs, which are the only values that the user can change in the spreadsheet, are in bright yellow and are the number of attackers (Att.), the number of defenders (Def.), whether or not the attacker may take an extra loss (Penalty) and the odds. Just below the user inputs and in dull yellow are the two possible, lower and upper, attack odds and their chance of happening assuming fractional odds. If the user isn’t playing with fractional odds then he only needs to input the odds as such (e.g., 20 instead of 20.6 or 4 instead of 4.333 in these two examples).
The calculator computes several probabilities and expected values for three different odds (weighted average, lower and upper odds). The weighted average is just that, the weighted average results of the lower and upper odds, weighted by the chance of those attacks. Results shown as percentages (e.g., 74.3%) are “probabilities” and without are expected values. PWIN is the probability that the attacker “wins” and is defined as the probability that all defenders are removed from the hex being attacked either by elimination, retreat or shattered. SHATTER and BREAKTHRO are the probabilities of getting a shatter or breakthrough result, respectively. As shown in the next post, if a breakthrough isn’t possible (i.e., penalty = 1) then the Blitz calculator automatically changes BREAKTHRO to SHATTER. Ex Def Kill is the expected number of defenders killed. Att Org is the probability that all surviving attackers remain organized, Att 1/2 Org is the probability that half of the surviving attackers (rounded up) remain organize and Att Disorg is the probability that all surviving attackers become disorganized. Att No Loss is the probability that the attacker takes no losses and Att Ex Lost is the expected number of attackers lost.

[rkr1958]

Example of a Blitz attack breakthrough result changing to shatter when there's a possibility for the attacker to take an extra loss.

[rkr1958]

Here's the first example of me making use of my 2D10 CRT calculator in a solo game that I've just started. The situation is that the Japanese have a chance for a low odds, +4.333 attack on a lone Mao. What surprised me was that even with HQ support from Mao, which reduces the attack to +2.833 there's a slightly better than 50/50 chance (i.e., Ex Def Kill = 0.53) of elimination Mao. For the Chinese, it obvious that the odds of saving Mao dramatically increase if the Chinese select Blitz versus Assault.

So as the Japanese player and based this calculator I decide to make the attack. As the Chinese player I used this calculator to choose Blitz and provide HQ support from Mao.

NOTE: Ex Def Kill, which is the expected number of defenders kill, is only equivalent to the probability that the defender is killed when the number of defenders is 1 (i.e., Def. = 1). For this example, which there was only one defender Mao, Ex Def Kill was both the expected number of defenders killed and the probability that Mao was eliminated.

[rkr1958]

Example 2.

In example 2, the Germans are using the calculator to determine how best to finish off the Poles in their final assault on Poland. For example, should they risk their engineer and use engineer support? They have 5 tactical factors available, do they need them for ground support? Also, both Von Leeb and Rundstdet HQ's are participating in the attack. Is HQ support needed? If so, which HQ should provide the support?

So, I used the calculator to determine with engineering support that the Germans would guarantee a win (i.e, PWIN went from 98.6% to 100%). Now with the win guaranteed, I use the calculator to best figure out how to keep my engineering unit alive and too support mobilization from Poland to the Western Front. I decide not to use ground support and to use HQ support from Rundstedt so those units could rebase back to the Western Front next axis impulse.

So with +1 HQ support using Von Leeb the odds of not taking a loss was 99.6%, or alternately, the odds of taking a loss and losing the engineer unit was 0.4%. An acceptable risk I determined so I made the attack using +1 HQ support from Von Leeb.

[rkr1958]

I've attached the "initial" version (0.0.0) of my 2D10 CRT Excel based calculator. Try it out and let me know what you think. Even better, make improvements/mods and post them.

[juntoalmar]

Thanks a lot for your post. But I'm not really sure if I understand correctly. The user has to fill the dark yellow cells: Att, Def, Penalty and Odds, right?

- What should you enter on the penalty cell?

- Do you need to enter the odds manually, or can I just add a formula to calculate it from the Att. & Def. cells?

- But what should you enter, for instance, in a combat 16:5 where the defender uses HQ support and +4 ground support?

- PWIN I guess is the probability of victory. But what does exactly mean? A result of 1/B is considered “win”?

- What does “Att Ex Lost” cell mean?


Note to Mac Users: Att 1/2 Org cell need a change to work on Mac, “0.5” should be changed in the formula to “0,5”.

[rkr1958]

ORIGINAL: juntoalmar

Thanks a lot for your post. But I'm not really sure if I understand correctly. The user has to fill the dark yellow cells: Att, Def, Penalty and Odds, right?

- What should you enter on the penalty cell?

- Do you need to enter the odds manually, or can I just add a formula to calculate it from the Att. & Def. cells?

- But what should you enter, for instance, in a combat 16:5 where the defender uses HQ support and +4 ground support?

- PWIN I guess is the probability of victory. But what does exactly mean? A result of 1/B is considered “win”?

- What does “Att Ex Lost” cell mean?


Note to Mac Users: Att 1/2 Org cell need a change to work on Mac, “0.5” should be changed in the formula to “0,5”.

All four numbers in the dark yellow cells must be entered manually.
Att. is the number of units attacking.
Def. is the number of units defending.
Penalty needs to be either 0 or 1. 1 if the attacker could take an extra loss for attacking in non-clear terrain, bad weather, etc. or on the Blitz if a breakthrough isn't possible due to those factors.
Odds are the odds entered by the user. These should be the final odds after everything (e.g., HQ support, units disorganized, engineer support, offensive and defensive air support). If you're an experienced WiF player, which I'm not, you can calculate the final odds yourself or let MWiF, which I do, do if for you

PWIN is the probability that the defender will either be eliminate or forced to vacate the hex AND the attacker has at least 1 unit surviving that could advance into the hex.

Att No Loss is the percentage of time that the attacker will NOT take any losses.

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

Att Org is the percentage of time that all attackers will remain organized, of course with the exception of an HQ unit used for support.

Att 1/2 Org is the percentage of time that the attacker will have to disorganize half of his surviving attackers (rounded down).

Att Disorg is the percentage of time that all surviving attackers will be disorganized.

[juntoalmar]

Thanks a lot again. [&o]

My apologies, because I read the post long ago and downloaded the Excel and just recently looked at the file. I've just realised that most of my questions were explained in your first post (and already forgotten)... I should have read it carefully again [8|]

[juntoalmar]

BTW, I may have found a bug in the spreadsheet.

In sheet "calculator", column O the formulas for "assault" and "blitzkrieg" are significantly different.

Assault
O3 =IF(OR(F24=1;J3>=M3);1;0)*N3

Blitzkrieg
O31 =IF(F31=1;1;0)*N31


Results for assault, are calculated with formulas that take values from different rows (assault on row 3, takes numbers from row 24). That means, for instance, that cells O7, O8 are trying to read values of empty cells out of the table (F28, F29).

O26 takes values directly out of the blitz table (instead of assault).

[rkr1958]

ORIGINAL: juntoalmar

BTW, I may have found a bug in the spreadsheet.

In sheet "calculator", column O the formulas for "assault" and "blitzkrieg" are significantly different.

Assault
O3 =IF(OR(F24=1;J3>=M3);1;0)*N3

Blitzkrieg
O31 =IF(F31=1;1;0)*N31


Results for assault, are calculated with formulas that take values from different rows (assault on row 3, takes numbers from row 24). That means, for instance, that cells O7, O8 are trying to read values of empty cells out of the table (F28, F29).

O26 takes values directly out of the blitz table (instead of assault).

Report

Thanks! I'll take a lot and it, make the necessary fix and post an update.

[rkr1958]

ORIGINAL: juntoalmar

Assault
O3 =IF(OR(F24=1;J3>=M3);1;0)*N3

Nice catch, the formula should be O3 =IF(OR(F3=1,J3>=M3),1,0)*N3

Correction made and the updated spreadsheet will be uploaded with some cell validation features. I'll explain when I get it all in place.

[rkr1958]

EXCEL Based 2D10 CRT calculator v001. Includes a fix to the bug found by "juntoalmar" and validation checks of the player inputs, which are the dark yellow cells.

[rkr1958]

v001 attached.

[juntoalmar]

Thanks again.

I'm kind of surprised that there are not many more comments here, as this spreadsheet is fantastic!

[rkr1958]

ORIGINAL: juntoalmar

Thanks again.

I'm kind of surprised that there are not many more comments here, as this spreadsheet is fantastic!

I appreciate it and thanks for taking a look at the equations.

If you have time take a lot at the equations in cells H3, H27, H31, H55, R3, R27, R31 & R55. Specifically, I had to modify those equations to handle the unlikely cases of odds from -2 to -21. The original version wasn't working right for odds < -1. I think I got that corrected now but a second look, if you have the time, at the equations wouldn't hurt.

[paulderynck]

I have not looked at the cells but conceptually, this is how it should be done for odds of less than 1:1.

FREX 6 attacking 16 means a 1 to 3 using 5 and one third factors. But the difference between a 1:3 and a 1:2 is a Die Roll Modifier (DRM) of TWO, whereas the fractional resolution is supposed to yield the chance to give an increase of ONE to the DRM. To increase the DRM by 1, you need to go up from a ratio of 1 to 3 to a ratio of 1 to 2 point 5. To get that against 16, you need to attack with 6.4. So the fraction is 2/3 (the left over part after the 5 & 1/3) divided by (6.4 minus 5 & 1/3, i.e. the distance to go to get the next odds up) which equals point 62. So if playing fractional odds, you'd need to roll a 6 or less to have a DRM of -1, Roll above that and it's a DRM of -2. (or with MWiF the fractionals are calculated and tested out to 3 decimal places.)

Similarly, another example for 9 attacking 20 means a 1 to 2.5 using 8 factors. To increase the DRM by 1, you need to go up from a ratio of 1:2.5 to a ratio of 1:2. To get that against 20 you need to attack with 10. So the fraction is 1 (the left over part after the 8) divided by 10 minus 8, which is 1/2. This is a simpler and more intuitive example compared to the one above. The attacker is exactly half way between odds of 1:2.5 and odds of 1:2. From the 2D10 chart, 1:2 is a DRM of zero and 1:3 is a DRM of -2 so 1:2.5 must be a DRM of -1.You have to roll for a 5 or less to make it a DRM of zero. On a 6 to 10 it stays a DRM of -1.

[rkr1958]

ORIGINAL: paulderynck

I have not looked at the cells but conceptually, this is how it should be done for odds of less than 1:1.

FREX 6 attacking 16 means a 1 to 3 using 5 and one third factors. But the difference between a 1:3 and a 1:2 is a Die Roll Modifier (DRM) of TWO, whereas the fractional resolution is supposed to yield the chance to give an increase of ONE to the DRM. To increase the DRM by 1, you need to go up from a ratio of 1 to 3 to a ratio of 1 to 2 point 5. To get that against 16, you need to attack with 6.4. So the fraction is 2/3 (the left over part after the 5 & 1/3) divided by (6.4 minus 5 & 1/3, i.e. the distance to go to get the next odds up) which equals point 62. So if playing fractional odds, you'd need to roll a 6 or less to have a DRM of -1, Roll above that and it's a DRM of -2. (or with MWiF the fractionals are calculated and tested out to 3 decimal places.)

Similarly, another example for 9 attacking 20 means a 1 to 2.5 using 8 factors. To increase the DRM by 1, you need to go up from a ratio of 1:2.5 to a ratio of 1:2. To get that against 20 you need to attack with 10. So the fraction is 1 (the left over part after the 8) divided by 10 minus 8, which is 1/2. This is a simpler and more intuitive example compared to the one above. The attacker is exactly half way between odds of 1:2.5 and odds of 1:2. From the 2D10 chart, 1:2 is a DRM of zero and 1:3 is a DRM of -2 so 1:2.5 must be a DRM of -1.You have to roll for a 5 or less to make it a DRM of zero. On a 6 to 10 it stays a DRM of -1.

Great info. Thanks!

[juntoalmar]

ORIGINAL: rkr1958

I appreciate it and thanks for taking a look at the equations.

If you have time take a lot at the equations in cells H3, H27, H31, H55, R3, R27, R31 & R55. Specifically, I had to modify those equations to handle the unlikely cases of odds from -2 to -21. The original version wasn't working right for odds < -1. I think I got that corrected now but a second look, if you have the time, at the equations wouldn't hurt.

I will take a look later today.

[celebrindal]

Looks great, played around with it a bit.. couple of little add-ons might be nice.
1. To the right of the table allow a person to enter a column of numbers for Att/Def so that it calculates the attack odds (initially)
2. Allow a person to enter in the all the +/- either as a single number or a column.

The reasons for the above are if you are wanting to calculate a quick attack possibility prior to moving the units, say it isn't your turn, then you can do so. Otherwise you have to open up a sheet or calculator and do all of that outside of the sheet.

[juntoalmar]

ORIGINAL: celebrindal

1. To the right of the table allow a person to enter a column of numbers for Att/Def so that it calculates the attack odds (initially)

The reasons for the above are if you are wanting to calculate a quick attack possibility prior to moving the units, say it isn't your turn, then you can do so.

But then, you are missing the penalties for attacking through a river, against a mountain hex... The spreadsheet won't be able to calculate that. I think it's easier to make a "trial attack" to see the final odds.