Napoleonic Wargame Club (NWC)

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

_________________


Generalfeldmarschall Wilhelm Prinz Peters von Dennewitz

3. Husaren-Regiment, Reserve-Kavallerie, Preußischen Armee-Korps

Honarary CO of Garde-Ulanen Regiment, Garde-Grenadier Kavallerie

NWC Founding Member

For Club Games: I prefer the Single Phase mode of play. I prefer to play with the following options OFF:

MDF, VP4LC, NRO, MTD, CMR, PR, MIM, NDM, OMR (ver 4.07)

Hi Bill,
Hope I got it right. The file is attached.

_________________
General-Leytenant Alexey Tartyshev
Leib-Guard Preobrazhensky Regiment (Grenadier Drum)
1st Brigade
Guard Infantry Division
5th Guard Corps


(I don't play with with ZOC kills and Rout limiting ON)

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.

_________________


Generalfeldmarschall Wilhelm Prinz Peters von Dennewitz

3. Husaren-Regiment, Reserve-Kavallerie, Preußischen Armee-Korps

Honarary CO of Garde-Ulanen Regiment, Garde-Grenadier Kavallerie

NWC Founding Member

For Club Games: I prefer the Single Phase mode of play. I prefer to play with the following options OFF:

MDF, VP4LC, NRO, MTD, CMR, PR, MIM, NDM, OMR (ver 4.07)

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.

_________________


Generalfeldmarschall Wilhelm Prinz Peters von Dennewitz

3. Husaren-Regiment, Reserve-Kavallerie, Preußischen Armee-Korps

Honarary CO of Garde-Ulanen Regiment, Garde-Grenadier Kavallerie

NWC Founding Member

For Club Games: I prefer the Single Phase mode of play. I prefer to play with the following options OFF:

MDF, VP4LC, NRO, MTD, CMR, PR, MIM, NDM, OMR (ver 4.07)

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.

_________________

Step 4 get very confused about those formulas

_________________
Marechal Knox

Prince d'Austerlitz et Comte d'Argentan
Ordre national de la Légion d'honneur

"What is history but a fable agreed upon"

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

_________________

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.

_________________

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

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.

My head HURTS after reading this thread.

_________________
Ernie Sands
1ère Brigade of 2ème Division de Grosse Cavalerie, Réserve de Cavalerie
de la Grande Armée
President, Colonial Campaigns Club

Try Sesame Street!

_________________


Generalfeldmarschall Wilhelm Prinz Peters von Dennewitz

3. Husaren-Regiment, Reserve-Kavallerie, Preußischen Armee-Korps

Honarary CO of Garde-Ulanen Regiment, Garde-Grenadier Kavallerie

NWC Founding Member

For Club Games: I prefer the Single Phase mode of play. I prefer to play with the following options OFF:

MDF, VP4LC, NRO, MTD, CMR, PR, MIM, NDM, OMR (ver 4.07)