So my work continues on an Excel probability calculator. A friend has asked me if I can simulate rerolls along with simulated roll sets.
Here's what I've done to accommodate that:
The RANDARRAY generates an array of B2 number of sets of rolls of 6 three-sided dice, counting all rolls that aren't 1's as a win. The MMULT then counts the wins on each row.
In order to simulate rerolls I created a second RANDARRAY that rolls the same number of sets as B2, but with B3 columns for the number of rerolls I want to allow.
The trouble I was having was I only wanted to reroll ones in each test-- so if I had three total rerolls available, but a given test only had one miss, I only wanted to count one of the rerolls. Likewise if I had four misses on the test but only three rerolls available I only wanted to reroll three times. The solution I arrived at was to create some helper columns to count the number of misses, and compare that to the total number of permitted rerolls and then compare the number of hits on the reroll array to the number of misses in the original array and add whichever number is lower to get the adjusted result (with some error handling if the initial test had no rerolls available).
However, my concern is that my solution skews the probability too much. For instance, if a particular test had only one miss, it may only add a single win from the reroll array, but it's still effectively rolling three dice even though it should be only rolling one. As can be seen in Row 8, it correctly counted counted wins, but you can see the first test was a 1, so if this test had been done by hand that reroll would have only added a single win, not two.
Mathematically a reroll is identical to "Roll two dice but only count it once even if they both hit." Is there another solution that could better achieve what I am trying to accomplish?
Here's what I've done to accommodate that:
| prop calc.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Tests | 10 | ||||||||||||||
| 2 | Rerolls | 3 | ||||||||||||||
| 3 | ||||||||||||||||
| 4 | ||||||||||||||||
| 5 | Misses | RR Allow | Reroll Array | RR Result | Adj Wins | |||||||||||
| 6 | 2 | 1 | 2 | 2 | 3 | 1 | 4 | 2 | 2 | 3 | 1 | 2 | 2 | 6 | ||
| 7 | 3 | 3 | 2 | 2 | 3 | 1 | 5 | 1 | 1 | 1 | 2 | 1 | 1 | 6 | ||
| 8 | 1 | 2 | 2 | 1 | 2 | 3 | 4 | 2 | 2 | 1 | 3 | 2 | 2 | 6 | ||
| 9 | 3 | 3 | 1 | 3 | 3 | 1 | 4 | 2 | 2 | 2 | 3 | 1 | 2 | 6 | ||
| 10 | 3 | 2 | 1 | 3 | 3 | 2 | 5 | 1 | 1 | 3 | 3 | 1 | 2 | 6 | ||
| 11 | 1 | 3 | 1 | 3 | 3 | 3 | 4 | 2 | 2 | 1 | 3 | 3 | 2 | 6 | ||
| 12 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 5 | 3 | 2 | 1 | 3 | 2 | 3 | ||
| 13 | 3 | 1 | 2 | 2 | 2 | 1 | 4 | 2 | 2 | 3 | 2 | 2 | 3 | 6 | ||
| 14 | 3 | 3 | 3 | 3 | 3 | 1 | 5 | 1 | 1 | 3 | 3 | 2 | 3 | 6 | ||
| 15 | 1 | 1 | 3 | 2 | 3 | 3 | 4 | 2 | 2 | 1 | 1 | 3 | 1 | 5 | ||
| 16 | ||||||||||||||||
| 17 | ||||||||||||||||
| 18 | ||||||||||||||||
reroll test | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A6:F15 | A6 | =RANDARRAY($B$1,6,1,3,TRUE) |
| G6:G15 | G6 | =MMULT(--(A6#>1),SEQUENCE(COLUMNS(A6#),,,0)) |
| H6:H15 | H6 | =6-G6# |
| I6:I15 | I6 | =IF(6-G6#<=$B$2,6-G6#,$B$2) |
| J6:L15 | J6 | =IFERROR(RANDARRAY($B$1,$B$2,1,3,TRUE),0) |
| M6:M15 | M6 | =MMULT(--(J6#>1),SEQUENCE(COLUMNS(J6#),,,0)) |
| N6:N15 | N6 | =IF(I6#<=M6#,I6#,M6#)+G6# |
| Dynamic array formulas. | ||
The RANDARRAY generates an array of B2 number of sets of rolls of 6 three-sided dice, counting all rolls that aren't 1's as a win. The MMULT then counts the wins on each row.
In order to simulate rerolls I created a second RANDARRAY that rolls the same number of sets as B2, but with B3 columns for the number of rerolls I want to allow.
The trouble I was having was I only wanted to reroll ones in each test-- so if I had three total rerolls available, but a given test only had one miss, I only wanted to count one of the rerolls. Likewise if I had four misses on the test but only three rerolls available I only wanted to reroll three times. The solution I arrived at was to create some helper columns to count the number of misses, and compare that to the total number of permitted rerolls and then compare the number of hits on the reroll array to the number of misses in the original array and add whichever number is lower to get the adjusted result (with some error handling if the initial test had no rerolls available).
However, my concern is that my solution skews the probability too much. For instance, if a particular test had only one miss, it may only add a single win from the reroll array, but it's still effectively rolling three dice even though it should be only rolling one. As can be seen in Row 8, it correctly counted counted wins, but you can see the first test was a 1, so if this test had been done by hand that reroll would have only added a single win, not two.
Mathematically a reroll is identical to "Roll two dice but only count it once even if they both hit." Is there another solution that could better achieve what I am trying to accomplish?
