Napoleonic Wargame Club (NWC)
https://www.wargame.ch/board/nwc/

Command Test Calculator
https://www.wargame.ch/board/nwc/viewtopic.php?f=5&t=11169		 Page 1 of 1
Author:  Bill Peters [ Wed Nov 24, 2010 6:21 pm ]
Post subject:  Command Test Calculator
Guys - I put together a spreadsheet that shows how the Command Test calculation works. There is one part of it I cannot get to work: I would like to be able to run the entire routine and get it to show the result for the unit. I wanted to create a formula that would tally up the result and then apply it to the unit's die roll to see if it recovered from Disorder. If you can add in a calculation please send me the file or just post it here.

Its just a "test" sheet - it does not connect to the game in anyway but for those that like to tinker with the Command ratings to see how they work or get a result if you want a copy of it here it is (see attached file).
Author:  Alexey Tartyshev [ Thu Nov 25, 2010 2:05 am ]
Post subject:  Re: Command Test Calculator
Hi Bill,
Hope I got it right. The file is attached.
Author:  Bill Peters [ Thu Nov 25, 2010 11:04 am ]
Post subject:  Re: Command Test Calculator
Hey, nice try but what some folks fail to understand is that if ONE of the superior leaders fails his test then none of the bonuses from that level up are passed along to the unit. Only those bonuses from leaders in a continuous string of 1s pass along to the unit.

For example:

If the Army and Wing COs pass their tests, the Corps does not, the Division leader does and the Brigade leader does then the bonus is 2. Not the sums of all of the "1" values.

So in your sheet you have the bonus in that case being a 4 where it should be a 2.

Its a nice looking sheet too.

Thus for any command bonus value that is 0 you need to start the sum up all over again and continue on down to the unit with the bonus sum as long as the value for each test is 1. If you ever hit a "0" test result again you must toss out the bonuses.

Here is another case:

Army CO does NOT pass - 0
Wing CO does pass - 1
Corps CO does pass - 1
Div CO does NOT pass - 0
Brig CO does pass - 1

Bonus is 1. Not 3.

For a bit I was wondering if Boolean Algebra would work. This is similar to a logic problem I once worked in college (back in 1978 as a guy that should have still been in High School as a senior - I sure loved going to college when I was 17 though!) where you had a series of gates and you could get AND, OR, NAND, NOR and so on at each gate. I am not sure if Excel has expressions that we would need to solve this logic problem but I am sure that it does.

Hope that helps.
Author:  Bill Peters [ Thu Nov 25, 2010 4:06 pm ]
Post subject:  Re: Command Test Calculator
Ok - I had this wrong. The command test is a bonus passed to the leader to see if he passes his test and gains a higher rating with which he passes long to his unit.

Mark Jones gets the nod for correctly interpreting the manual and creating a spreadsheet that works.

I will let him post it here on the forum if he wants.
Author:  Kosyanenko [ Thu Nov 25, 2010 5:05 pm ]
Post subject:  Re: Command Test Calculator
Step 1: study the article here http://en.wikipedia.org/wiki/Law_of_total_probability

Step 2: suppose n=2, B1 - higher level leader fails the test, B2 higher level leader passess the test, A current level leader fails the test.

Then P(A)=P(A|B1)*P(B1)+P(A|B2)*P(B2)

P(B1) and P(B2) are cmputed on previous step.

P(A|B1)=(MaxRating-LeaderRating)/MaxRating. In our model we have MaxRating=6.
P(A|B2)=MAX((MaxRating-LeaderRating-CumulativeBonus);0)/MaxRating. Function MAX(*) must be present because if LeaderRating=A=6 we would have negative probabilities.

P(Not A)=1-P(A) - probability to pass the test.

Step 3: write the formulas in a spreadsheet. And enjoy the toy.
Author:  Colin Knox [ Thu Nov 25, 2010 5:26 pm ]
Post subject:  Re: Command Test Calculator
Step 4 get very confused about those formulas :mrgreen: :lol: :oops:
Author:  Kosyanenko [ Thu Nov 25, 2010 5:54 pm ]
Post subject:  Re: Command Test Calculator
Colin Knox wrote:
Step 4 get very confused about those formulas :mrgreen: :lol: :oops:

THOSE TINY AND SOFT!!!!! IT'S SO EASY IN NORMAL PROGRAMMING TOOLS, BUT IT'S SO BRAIN DESTRUCTIVE IN EXCEL. FINALLY I WILL POST THE FILE :evil:
Author:  Kosyanenko [ Thu Nov 25, 2010 6:40 pm ]
Post subject:  Re: Command Test Calculator
So...

STEP 0: Read the problem carefully. Twice.

Step 1: study the article here http://en.wikipedia.org/wiki/Law_of_total_probability Not used in the exercise, but very useful for understanding how it works :-)

Step 2: UDERSTAND THAT DUE TO THE NATURE OF THE TASK A COMPLETE SET OF OUTCOMES MUST BE EXAMINED. Read the problem again. Twice.

Step 3: construct a complete set of outcomes (leftmost set of figures). Each row represents a possible outcome of tests, where 1 stands for successful pass, 0 - fail.

Step 4: compute a bonuse a leader on each level receives given the outcome is like in step 3. (Second set of figures)

Step 5: compute effective leader's ratings. Check that under any nominal ratings sets effectives do not exceed MaxRating. (Third set)

Step 6: Compute a conditional probability for every leader's level to obtain the result contained in outcome. (i.e. if outcome is 1 1 0 1 0 it means that one needs to compute say probability for C to fail given that others are 1 1 1 0 respectively). (Fourth set)

Step 7: Compute probabilities of outcomes. (Second column from the right. Has bold borders)

Step 8: Make sure those probabilities being summed up give 1. If not, return to Step 3. (computed in the bottom of the column)

Step 9: Achieve the target, i.e. compute the probability for a unit to receive a command bonus. (right-bottom cell, has bold borders).

Step 10: Buy everyone a drink. Ehh... Well... Human only. Othervise Sir Muddy's horse will ruin it's health and lead you into povetry.
Author:  MCJones1810 [ Thu Nov 25, 2010 8:10 pm ]
Post subject:  Re: Command Test Calculator
Hey All:

Attached was my minor modification of Bill's spreadsheet. if you want to see how the failure of various leaders affects the outcome, simply change the Leader Ratings (Cells B6 through F6) to a 1. You may change any, or all of these values at will without harming the function of the spreadsheet. Most other cells contain formulas that should not be altered.

The surprising thing to me in this exercise is that all units, whether they be Old Guard or Opelchini, reorder on a 1 in 6 chance if they are not in command. I simply did not know that.

(See Andy, I actually can read the rules when I have to. :D Probably better to do that before a match though. :? )

Thanks for the thread, Bill. Yet again I learned something that I really needed to know.

(I know, Andy. I should just read the dang rules to start with. Sigh! :oops: )
Author:  Al Amos [ Thu Nov 25, 2010 8:28 pm ]
Post subject:  Re: Command Test Calculator
Mark,

Most of the game is based on a D6 (die 6), or random number from 1 to 6. I wish JT would've set it up on a D10 or D100 base, then more gradation could be used for leader and unit qualities.
Author:  Ernie Sands [ Thu Nov 25, 2010 11:39 pm ]
Post subject:  Re: Command Test Calculator
My head HURTS after reading this thread. :wink:
Author:  Bill Peters [ Tue Nov 30, 2010 10:55 am ]
Post subject:  Re: Command Test Calculator
Try Sesame Street! :mrgreen:
Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/