EXCEL Based 2D10 CRT Calculator. (Full Version)

All Forums >> [New Releases from Matrix Games] >> World in Flames >> The War Room



Message

rkr1958 -> EXCEL Based 2D10 CRT Calculator. (12/20/2016 6:35:48 PM)

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.


[image]local://upfiles/31901/33E9B8271E7C473FB86B545DF30D3F15.jpg[/image]



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (12/20/2016 6:37:12 PM)

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

[image]local://upfiles/31901/DAF40FE6371F4D78AF3F012FD2BEB401.jpg[/image]



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (12/20/2016 6:50:49 PM)

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.

[image]local://upfiles/31901/BE64EC11B0DF43F88A987745B8F43F07.jpg[/image]



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (12/20/2016 7:15:04 PM)

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.

[image]local://upfiles/31901/37B2D2AAC16F4C6181344995BAD351C1.jpg[/image]



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (12/21/2016 8:01:31 PM)

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/4/2017 9:04:29 AM)

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/4/2017 11:16:55 PM)

quote:

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/5/2017 10:44:14 AM)

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/5/2017 11:02:29 AM)

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/7/2017 9:07:31 PM)


quote:

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).

quote:

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



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (1/7/2017 10:35:15 PM)


quote:

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/8/2017 2:06:43 AM)

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.

[image]local://upfiles/31901/6766AFEBA9A542D99610CC38631BA9D3.jpg[/image]



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (1/8/2017 2:07:44 AM)

v001 attached.



juntoalmar -> RE: EXCEL Based 2D10 CRT Calculator. (1/8/2017 8:20:42 AM)

Thanks again.

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



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (1/8/2017 4:29:55 PM)


quote:

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/8/2017 6:55:24 PM)

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/8/2017 7:51:10 PM)


quote:

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/9/2017 6:38:03 AM)


quote:

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/29/2017 2:08:10 PM)

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 -> RE: EXCEL Based 2D10 CRT Calculator. (1/29/2017 2:17:38 PM)


quote:

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.



brian brian -> RE: EXCEL Based 2D10 CRT Calculator. (2/7/2017 3:11:30 PM)

No laptop access here right now, but curious - what is the % chance of taking a hex against two defenders on a straight +9 assault?



Courtenay -> RE: EXCEL Based 2D10 CRT Calculator. (2/7/2017 8:17:15 PM)


quote:

ORIGINAL: brian brian

No laptop access here right now, but curious - what is the % chance of taking a hex against two defenders on a straight +9 assault?

64% if three or more attackers or two and no extra loss.
55% if two attackers and extra loss.
45% if one attacker and no extra loss.
37% if one attacker and extra loss.

Calculated using my own Open Office combat probability calculator.

If there is interest, I will post it.



brian brian -> RE: EXCEL Based 2D10 CRT Calculator. (2/7/2017 11:25:21 PM)

Thanks!



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (2/9/2017 10:49:32 PM)


quote:

ORIGINAL: Courtenay


quote:

ORIGINAL: brian brian

No laptop access here right now, but curious - what is the % chance of taking a hex against two defenders on a straight +9 assault?

64% if three or more attackers or two and no extra loss.
55% if two attackers and extra loss.
45% if one attacker and no extra loss.
37% if one attacker and extra loss.

Calculated using my own Open Office combat probability calculator.


It's comforting for me that I got the same answers that you did when I ran Brian's numbers through my EXCEL based 2D10 CRT calculator.

quote:

ORIGINAL: Courtenay
If there is interest, I will post it.
Heck yea!



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (3/4/2017 9:33:43 PM)

Updated Calculator, v002.

I made some updates to my excel based 2D10 CRT calculator. These updates include:

1. Flag added if the attack is an invasion (1=yes, 0=no) and would result in the elimination of all attacking units if the hex isn't taken.

2. The probability that the attacker will lose 3, or more, units (Att 3 Loss).

I've include an example from one of my solo games in which I made use of the "invasion" option. In this example the Italians had 3 division in position to attempt an invasion of Malta. After shore bombardment and ground support the final odds were +2.6. A 41% chance to take Malta at the cost of losing 3 divisions seemed well worth the risk. The expected "cost" was 2.29 division but, of course, if the attack failed (58.9%) then all three division would be lost.

I'm finding that my calculator is better helping me decide on what are acceptable and unacceptable risks. In this example a +2.6 assault and a 41% chance of winning was acceptable.

[image]local://upfiles/31901/0B22A5B6B9E54D56A735417BB730FA36.jpg[/image]



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (3/4/2017 9:34:23 PM)

v002 of the calculator.



Dabrion -> RE: EXCEL Based 2D10 CRT Calculator. (4/20/2017 2:50:59 AM)

Here is my attack planner: https://docs.google.com/spreadsheets/d/14z6MagQbgM6H6XrHF04KFTAJi0FMDU1H53wVru2Tmx4/edit#gid=3906537



Grotius -> RE: EXCEL Based 2D10 CRT Calculator. (1/4/2018 5:10:55 AM)

Ronnie, this is a great worksheet; thanks for posting it. One newbish question about the meaning of "penalty". You say:

quote:

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.


I understand everything you said up to "bad weather, etc", but not the clause on the Blitz and breakthrough. What rule are you referring to?

Incidentally, your spreadsheet brings home to me (a newb) how often the attacker has to take that extra loss when in the "penalty" situation. At least a third of the time, roughly speaking, it seems? And the "penalty" situation seems to arise constantly.




hazmaxed -> RE: EXCEL Based 2D10 CRT Calculator. (1/4/2018 7:25:58 PM)

I have not yet tried the spreadsheet, but I plan to. I've been "shooting from the hip" in my solitaire games, and have been somewhat perturbed by those "attacker takes an extra loss" results.

One question for you, rkr. In the screenshots of the validation checks in Post #11, I see the note that "Att. must be a whole number between 1 and 32." Why an upper limit of 32? Isn't the maximum number of units that can attack a single hex 21? (3 in each of the 6 adjacent hexes plus 3 paradropped units in the attacked hex?)

Let me know if I'm missing something so I can start repeating Courtenay's signature line 100 times.



rkr1958 -> RE: EXCEL Based 2D10 CRT Calculator. (4/12/2019 7:25:09 PM)


quote:

ORIGINAL: hazmaxed
One question for you, rkr. In the screenshots of the validation checks in Post #11, I see the note that "Att. must be a whole number between 1 and 32." Why an upper limit of 32? Isn't the maximum number of units that can attack a single hex 21? (3 in each of the 6 adjacent hexes plus 3 paradropped units in the attacked hex?)
I'll take your word for it that 21 is the maximum number of attackers. I guess my computer "bias" got to me in choosing 32. That is 2^5 = 32, where 2^4 = 16. So 2^4 = 16 isn't large enough to cover the maximum number of attackers but 2^5 = 32. Not sure why I choose 32, but I think I like this explanation and I'm sticking to it. [8D]



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
3.4375