Pages

Thursday, March 17, 2022

Dueling Calculator: Building A Monte Carlo Simulation, Part II

Good morning gamers,

In our last post, we viewed how to set up a Monte Carlo simulation to give us a better understanding of how models do when fighting each other (rather than just generating statistical probabilities). We only focused on the dueling aspect of the game because . . . it was really involved. Today, we finish the discussion (for now) to get the basics of wounding into the simulation. Let's get started!

Building The Simulation: Setting Up Inputs

In our last post, we had to roll dice for both sides, identify penalties applied to those rolls, determine if boosting rolls is a good idea, and then figuring out the highest roll for each side. More than 60 columns later, we found "the winner." Today, we don't have to do as much - but we DO have to generate a ton of columns. Rolling to wound in MESBG is done by one side during each duel (generally speaking - some models, like Sharku or Sharkey, can deal damage to their aggressors when they lose a fight) and it's often resolved within a few seconds of viewing the roll. But here's what's going on during those handful of seconds:
  • We figure out how many dice each model will be rolling (factoring in whether or not the enemy is trapped and we double our dice);
  • We identify the wounding difficulty we need to reach to successfully wound;
  • We roll the dice and look at our initial results and identify any successful wounds;
  • We identify if we have any rerolls that we can use (which could be rerolling 1s or rerolling any failed To Wound rolls);
  • We identify which dice have succeeded in wounding after rerolling;
  • We identify how much Might we'd need to spend to boost a wounding roll; and
  • We spend Might to boost wounding rolls that we want to work.
In order to walk through these steps is going to take columns - lots of columns for clarity - and we're going to have to make a lot of assumptions that go back to our inputs page. 


Our inputs page has the following information that we can use:
  • Base Attacks
  • Charge Bonus
  • Whether the enemy is trapped
  • Wounding Difficulty
  • Whether the model rerolls 1s To Wound
  • Number of full rerolls the model receives
  • Might allocated to boosting wounding rolls
The important thing to keep in mind is that the "Trapped" section is independent of knock-down - is my opponent trapped based on his innate positioning (regardless of whether you charged the guy or not). Setting this up right is important since we're rolling to see who has priority.

Notice that we've also set up different inputs for rerolling 1s and full rerolls - since we can reroll as many 1s as we want when we Feint (or however else you reroll 1s) and we usually have a limited number of rerolls, we will need to keep track of which dice have been rerolled on 1s before we do full rerolls.

Okay, let's get into it!

Building The Simulation: Rolling To Wound (And Rerolling 1s)

Our first twelve columns that we need to add are going to identify the number of wounding dice that we'll be rolling and the wounding difficulty that each model needs. We can do this by using the following formula - checking to see which side won the fight, and then determining the number of base Attacks the model has, boosting the number by 1 if there's an Extra Attack bonus, and doubling the number of dice if the enemy is either already trapped (input) or if the model has the Knock-down bonus and is charging:

=IF($BN2=$AT$1,(duel_calculator!L$14+IF(AND(IFERROR(FIND("Extra",duel_calculator!L$8,1),0)>0,$D2=$AT$1),1,0))*IF(OR(AND(IFERROR(FIND("Knock",duel_calculator!L$8,1),0)>0,$D2=$AT$1),duel_calculator!$L$16="Yes"),2,1),(duel_calculator!L$33+IF(AND(IFERROR(FIND("Extra",duel_calculator!L$27,1),0)>0,$D2=$BM$1),1,0))*IF(OR(AND(IFERROR(FIND("Knock",duel_calculator!L$27,1),0)>0,$D2=$BM$1),duel_calculator!$L$35="Yes"),2,1))

We can then compute the difficulty for wounding with the following formula - checking to see who won and looking up the minimum wounding values based on the human-readable values in the input tab:

=IFERROR(IF($BN2=$AT$1,VLOOKUP(duel_calculator!L$15,dueling_probabilities!$AB$3:$AD$19,2,FALSE)&VLOOKUP(duel_calculator!L$15,dueling_probabilities!$AB$3:$AD$19,3,FALSE),VLOOKUP(duel_calculator!L$34,dueling_probabilities!$AB$3:$AD$19,2,FALSE)&VLOOKUP(duel_calculator!L$34,dueling_probabilities!$AB$3:$AD$19,3,FALSE)),"")

The result should look something like this:

Note that Elrond's wounding dice count changes from 3 to 4 when his team has priority (and the Cave Drake goes from 4 to 10) . . . it's working!

With the number of dice that we're rolling configured up and the difficulty we need to reach, we're now going to start rolling dice . . . and REALLY packing on the columns. :-) We're going to need five sets of columns (one for each potential wounding profile) and each will need 20 columns (plus a summary column for grouping). Each will need to see if the wounding profile is even being used (else display nothing) and then see if the number of dice that we have to roll is equal to or less than the numbered dice we're rolling: 

=IF($BU2="","",IF($BO2>=CA$1,RANDBETWEEN(1,6)&RANDBETWEEN(1,6),""))

We can copy these formulas to the right and get the following look (I've copied these down 20 rows so you can see some differences between which side is winning fights):

We're rolling twice all the time - to make things easier on the computational end, we treat a wounding difficulty of a 4+ as a 4+/1+ as discussed in a previous post.

As we move from ProfileA to ProfileB, we're going to need to make minor adjustments to the $BU column and the $BO column. These column references will, naturally, be changed as we go into Profiles C, D, and E as well. I'm going to spare you the picture of >100 columns though - trust me that you can copy these over four times and you're good.

Now this is the RAW wounding roll - the first time you cast the dice. Our next step is to check and see if the model is allowed to reroll 1s - and if it is, we want to reroll it now (since we don't want to waste a full reroll on a 1 if we reroll 1s as well). To do this, we need 21 columns/profile (yes, that's 105 more columns) and see if each profile has the Reroll 1s enabled in the inputs tab. If so, we check the first and second values for 1s - and if any are 1s, we generate a new random number (otherwise we return the original number). Note that because we have the inputs page being referenced, we need to check which side won the fight - we could solve this by adding more columns after the wounding difficulty for whether rerolling 1s are enabled or the number of full rerolls in play (I have skipped this because I can):

=IF(OR(AND($BN2=$AT$1,NOT(duel_calculator!$L$18="Yes")),AND($BN2=$BM$1,NOT(duel_calculator!$L$37="Yes"))),CA2,IF(MID(CA2,1,1)="1",RANDBETWEEN(1,6),MID(CA2,1,1))&IF(MID(CA2,2,1)="1",RANDBETWEEN(1,6),MID(CA2,2,1)))

Adjust this formula for each of the 5 profile slots (though profiles C, D, and E don't need to reference the inputs page for the horizontal team since they only have 3 profiles) and you get the rerolls like this:

The Blue circles indicate cases where a 1 was rolled on the first dice roll and was rerolled and got something other than a 1 - but sometimes, you get a 1 on the second roll too (which is reflected in the red circles). The key take-away, though, is that there are a surprising number of 1s getting rerolled . . . food for thought for anyone who doesn't see that as a useful ability . . .

The final thing we can do is get the initial wounding successes - things that, after we spend no resources or full rerolls are "successful". We'll need another 105 columns and compare each of the wounding values against the original wounding difficulty:

=IF(GC2="","",IF(MID(GC2,1,1)>=MID($BU2,1,1),"S",MID(GC2,1,1))&IF(MID(GC2,2,1)>=MID($BU2,2,1),"S",MID(GC2,2,1)))

Any successful wounds will be represented by an "SS" value - which means each value was equal to or higher than the difficulty value. After adjusting the columns for each profile ($BU in this case), we can do a COUNT formula in the summary column to identify how many wounds we've dealt:

=COUNTIF(KE2:OE2,"SS")

And with that, we have our initial successes! But we're not done yet - we now need to see if we have full rerolls - and whether we're willing to boost our wounding dice with Might (we're almost there, I promise).

Building The Simulation: Rerolling And Boosting Wounding Dice

Rerolls are actually complicated in MESBG - even though our brains determine rerolls quite quickly. Rerolling is difficult in MESBG for two reasons: first and foremost, you can't reroll a die you've already rerolled (so in our builder, we'll need to account for dice that have been rerolled already because you got a 1 the first time and can reroll those). Second, you need to determine if you want to reroll a die before you spend Might - and if you're one-pip away from wounding something, you might want to skip the reroll and just boost the roll. This kind of thinking involves . . . quite a bit of thinking, so just know that for now at least, we're not going to account for this: a failure is a failure and deserves a reroll.

To determine our full rerolls, we're going to begin by finding our reroll candidates - anything that isn't an "SS" after the initial rolling. We will go ahead and reroll the dice now, working from left to right:

=IF(OR(AND($BN2=$AT$1,duel_calculator!$L$17>0),AND($BN2=$BM$1,duel_calculator!$L$36>0)),IF(KE2="","",IF(MID(KE2,1,2)="SS","SS",IF(MID(KE2,1,1)="S","S",RANDBETWEEN(1,6))&IF(MID(KE2,2,1)="S","S",RANDBETWEEN(1,6)))),KE2)

When we copy these to the right (adjusting for the input lookups, per the usual), it should look like this:

It's a bit counter-intuitive to get the rerolls now - and you could probably adjust your spreadsheet to have this come later if you wanted to - but the blue circles indicate the first reroll we're going to look at that actually turns into a wound (while the red circles indicate the first reroll we're going to find that fails to wound). Since Elrond only gets 1 full reroll, he should get 6/15 successful rerolls (which is pretty close to the probabilistic expectation).

This gives us the rerolls - but before we add these results, we need to determine if we can reroll them or not (we're going to output 0s or 1s in each column to show whether or not we can reroll that result or not):

=IF(KE2="","",IF(MID(KE2,1,2)="SS",0,IF(AND($BN2=$AT$1,SUM(SH2:$SH2)<duel_calculator!$L$17),1,IF(AND($BN$2=$BM$1,SUM($SH2:SH2)<duel_calculator!$L$36),1,0))))

It should look like this:

Lines up with what we had before - looking good!

Okay - our next section of columns compares the 0/1 values from the reroll section and gives us new values:

=IF(OR(SI2=0,SI2=""),KE2,IF(OG2="SS",OG2,IF(MID(OG2,1,1)>=MID($BU2,1,1),"S",MID(OG2,1,1))&IF(MID(OG2,2,1)>=MID($BU2,2,1),"S",MID(OG2,2,1))))

We copy these across (fixing that $BU column references) and down and it looks like this:

Look - our predictions were right!

Okay - we've rerolled everything we can, so we're at the end of the process: spending Might to boost our rolls. Like rerolling, this is easy for our brains and harder for machines. We're going to compute how much Might to boost each value, which is a simple subtraction problem for each of the two wounding elements (unless, of course, you have a "SS" value already):

=IF(WK2="","",IF(MID(WK2,1,1)="S","0",MID($BU2,1,1)-MID(WK2,1,1))&IF(MID(WK2,2,1)="S","0",MID($BU2,2,1)-MID(WK2,2,1)))

We're then going to go from left to right and see if we have enough Might allocated to boost a roll:

=IF(AAM2="","",IF(SUM($AEN2:AEN2,MID(AAM2,1,1))<=duel_calculator!$L$19,MID(AAM2,1,1),0))

Copy this down and over (you know the drill) and you should have a formula that looks at each roll that was made and how much you need to boost it by. Because none of the models I have in this scenario boost their rolls, I'm going to spare you the really long picture that has a bunch of zeros on it (that and I've been at this for hours, so . . .).

Last bit of calculating: we compare whether a roll is boosted, generate successes, and report the unadjusted value if we didn't have enough:

=IF(AEO2="","",IF(OR(AEO2=0,AEO2="0"),WK2,IF(MID(WK2,1,1)="S","S",MID(WK2,1,1)+MID(AAM2,1,1))&IF(MID(WK2,2,1)="S","S",MID(WK2,2,1)+MID(AAM2,2,1))))

We copy this down and across and make one last section to compare our boosted values to the wounding difficulty (these formulas will look very familiar, as we've done it before):

=IF(AIQ2="","",IF(MID(AIQ2,1,1)>=MID($BU2,1,1),"S",MID(AIQ2,1,1))&IF(MID(AIQ2,2,1)>=MID($BU2,2,1),"S",MID(AIQ2,2,1)))

And once this is in, we can count all of the "SS" values for each profile (see first formula below) and find our final successes (second formula):

=COUNTIF($AMS2:$ANL2,"SS")
=SUM(ANM2:AQS2)

In the end, you should have something like this:

The Cave Drake won 4/20 fights and dealt [1, 4, 6, 7] wounds - pretty good all things considered. Elrond and his boys won the other 16 fights and dealt somewhere between 0-4 wounds each time (but usually around 3 wounds/fight).

And we've now computed all of the wounds dealt by the winning side! Copy these formulas down and we're done . . . at long last . . . kind of. Chances are good that when you copied your cells all the way down, the spreadsheet began to crawl - that's because of all the calculations we have going on now . . . you may want to consider to turn off the auto-compute option in Excel and manually tell it to compute by hitting "Save".

Building The Simulation: Summary Results

We haven't factored in Fate saves yet, but those are easy (kind of). We'll tackle these next time, when we cover things like heroic actions that deplete our Might. In the meantime, here are some of the things you can compute given all of this information.

First and foremost, you can compute the expected wounds dealt by each side - which is just averaging the number of wounds each side dealt over the 2000 trials. You can similarly compute the expected wounds when you win (which is found by summing the number of wounds each side dealt divided by the number of times each side won). Similarly, by subtracting the "raw" wounds from the rerolled wounds - and subtracting the rerolled wounds from the "final" wounds - you can compute the number of wounds dealt over 2000 trials from rerolls and the number of wounds dealt over 2000 trials from boosting:


You can also compute your chances of dealing X wounds by counting the times that X wounds or more were scored and dividing by the number of times each side won - the equation looks something like this:

=COUNTIFS(<column_for_winner>,<side>,<column_for_final_wounds>,">=X")

When you modify the side that won and the X value, you get the following tables for expected wounds (I've divided by 2000 - the number of trials, which tells you the likelihood that you have of getting X wounds if you don't know if you've won yet):


Over-simplified summary: over 3 rounds, it would not be unreasonable to expect the Cave Drake to win one of those rounds and deal 1-2 wounds to Elrond. In rejoinder, Elrond and his boys should win 2 fights and deal 1-3 wounds each time (so about 4 wounds to the Cave Drake in return). Looks like Elrond, with some help, can really take it to the Cave Drake, eh? Well, if the Cave Drake chooses not to kill of Elrond's support and if no one runs out of stats . . .

Conclusion

And with that, we have a basic fight mapped out for MESBG. It's very simplistic though - and actually, until we factor in Fate points, we're really not done with the Fight phase, now are we? So, in our next post, we'll be covering two things: spending heroic resources (not only spending Fate points, but also spending Might for heroic actions) and being able to change your simulation to have sequential trials instead of independent trials (so Might and Fate, once spent, are gone until you die and the fights reset). Some of these things are quite valuable - but how do you factor them in? When we come back to this series (it'll be a while - I'm burnt out), we'll cover how to build these into your simulation. Until next time, happy hobbying!

No comments:

Post a Comment