Dice probability - formula for Excel

[MGrant]

In a game we have a dice rolling mechanic that the dice results contribute to hitting the target and then penetrating armor. The left over dice result in damage.

Here is an actual dice rolling mechanic.

10 dice (d6 like normal gambling dice) hitting on 3,4,5,6 chances, ( 0.6667 % ) and then penetrating armor on 4, 5 and 6, ( 0.5%). the result is 3.3 %

So there would be 10 dice, rolled once for the first result. The successes are used for the second roll penetration results, so in this case about 6.7 dice.
Those 6.7 dice are rolled a second time which are successful on 50% in this case, doing damage to the target with what is successful. in this case 3.3 damage.
This formula I have. Its the other way that I need.

Looking at a target that can handle 11 damage for example, I would like a formula that will be used in Excel that shows how many dice will need to be rolled on average to put that much damage on a given target using the same dice rolling mechanics.

What I would like to see in the result is how many dice it takes to reach 11 damage on average using the dice rolling probabilities above.

thanks for the instruction. And if I missed something, please let me know.

Mike

 

[tkhunny]

I can't quite follow your arithmetic.

1) You do not mean 0.6667%. You mean 0.6667 OR 66.67%
2) You do not mean 0.5%. You mean 0.5 OR 50%
3) How did you get from 66.67% and 50% all the way to 3.3%? Did you mean 33%?
4) You're rolling partial dice?

You're going to have to be substantially more careful with your notation or you will be forever confused.

 

[MGrant]

Thankyou for the reply. I'm going with what I see works in Excel.
I think your asking for an explanation on the formula used to get the hit and damage results.

The chance to hit on the first roll of 10 d6 dice is 2/3. ( 4 results are successes )
On the second roll, using the successes, (6 dice at a guess) are the second results which are the total of complete damage successes. These will be at 50%, so 6 dice at 50% is 3. With the fractions, its 3.3 successes from 10 dice.

So 10 dice rolling, to hit will be 2/3 of the rolled dice. Those 2/3 are now rolled again and hit on 4,5,6 or 1/2 of the chances. That total is 3.3 when I calculated it.

The target of the dice has a "life count" which is 11. So one would need to attack 3-4 times with 10 dice to do 11 damage to the target in this game.

I need to know how many dice exactly it takes to take down a target with 11 life and I need that formula because this game has a number different targets that could be 18 life total or 28 life total. I need a formula that I can calculate the number of dice for each target. All I need is the formula for one and I can do the rest.

An example.

First roll the 3s 4s, 5s and 6s all are successes, so 6 dice were rolled again. 4,5,6 are all sucesses on the second roll. So it should be 3. however a second 5 was rolled instead of a 1, but thats fine. Its dice. If the target can take 11 damage, ( and only 3 were sucesses) I need an Excel formula that shows how many dice will be needed to do 11 damage.

 

[JeffM]

All this is virtually incomprehensible.

You roll 10 six-sided dice. What is the definition of success? For example, you say 3, 4, 5, 6. What does that mean? 3, 4, 5, or 6 what? It cannot be the sum of the spots because that ranges from 10 through 60.

 

[Romsek]

[MATH]\text{Ok, so you roll your first 10 dice. $h$ of these are 3 or greater and thus constitute a hit}\\ \text{You then roll $h$ dice and $d$ of these are 4 or greater and thus constitute getting through armor and causing 1 point of damage each}[/MATH]
[MATH]\text{You want to know the expected number of rounds your 11 hp newbie can survive}\\ P[dmg=d] = P[\text{d hits through armor}] =\\ \sum \limits_{h=d}^{10} P[\text{$d$ hits through armor|$h$ hits}]P[\text{$h$ hits}]=\\ \sum \limits_{h=d}^{10} \dbinom{h}{d}(1/2)^h \cdot \dbinom{10}{h}(2/3)^h (1/3)^{10-h} [/MATH]
[MATH]\text{We can evaluate this and get the distribution of the damage per round}\\ P[D=d] = \left( \begin{array}{ccc} 0 & \frac{1024}{59049} & 0.0173415 \\ 1 & \frac{5120}{59049} & 0.0867076 \\ 2 & \frac{1280}{6561} & 0.195092 \\ 3 & \frac{5120}{19683} & 0.260123 \\ 4 & \frac{4480}{19683} & 0.227608 \\ 5 & \frac{896}{6561} & 0.136565 \\ 6 & \frac{1120}{19683} & 0.0569019 \\ 7 & \frac{320}{19683} & 0.0162577 \\ 8 & \frac{20}{6561} & 0.00304832 \\ 9 & \frac{20}{59049} & 0.000338702 \\ 10 & \frac{1}{59049} & 0.0000169351 \\ \end{array} \right)[/MATH]
[MATH]\text{And we can find it's expectation}\\ E[D] = \dfrac{10}{3}\\ \text{So we expect our newbie to survive $\dfrac{11}{\frac{10}{3}} = 3.3$ rounds, i.e.}\\ \text{They'll survive 3 rounds on average but perish during the 4th}[/MATH]
Incidentally this turns out to be just a binomial distribution with $\displaystyle n=10,~p = p_h \cdot p_a = \dfrac 2 3 \cdot \dfrac 1 2 = \dfrac 1 3$

 

[MGrant]

Very interesting. This is helpful.

However, I know this is complicated for me to explain. And I'm not speaking great math variables here. Thank you for your patience, and Im learning how to explain this better and we are getting very close.

What I've learned from the equation above is how many times I'm going to be rolling 10 dice to defeat a target with 11 roll successes.

Not really what Im looking for.


"I need an Excel formula that shows how many dice will be needed to do 11 damage. "

The attacker only gets one attempt and is not limited to 10 dice. If I have an army of models to use, I want to select the one with the right amount of attacks for the job and use other models elsewhere. With those tactics in mind, it establishes purpose for knowing why one needs to know how many dice it will take to do 11 damage to the target.


A given Attacker has X dice to destroy a target. All the attackers I have have different attack numbers. Some have 10, some have 36 Attacks. Having a formula that tells me how many dice I need, tells which model would be better for the task. And, Each target could have a number from 8 to 28 damage to remove. 11 damage on the target is an example.


The sequence of rolling is twice and the successes from the first roll are the amount of dice used in the second roll.

The sequence of dice rolling starts with an Attack skill which is either 1/3, 1/2, or 2/3 success. In the example, I used 2/3 success. ( rolling, 3 4 5 and 6 count as successes) Those successes are calculated again with the defense penetration roll. On average, 10 attacks will have 6 successes shown in picture I used above. However dice are chances, so sometimes they will not always have that result. The average number is fine to calculate. Back on penetration (2nd roll) roll, Most targets reduce the successes by 2/3 with armor, so the game uses 1/3, 1/2, or 2/3 to calculate the penetrating armor successes. In all cases 1 is always a miss, but that's not really relevant to the equation. Eventually I will understand the important parts of the equation that I can take it to a formula in Excel.


What I can calculate right now is how much damage 10 dice will do. The question might be, why doesn't this solve the problem then? Because I only know how much life the target has, not the amount of attacks I need. In this chart, I need to know how many dice to start the rolling to remove all 11 life.

Since I know my target's life total, I need to roll in one attack attempt (Hit and penetration rolls) to destroy a target with 11 life.



Two models stand on a table.

Model 1 and Model 2

Model 1 gets to attack model 2 because its model 1 turn.

Model 1 wants to destroy model 2. The game designers have established that Model 2 is an Elf will 11 damage. And its armor is 50% reduction. Model 1 has an attack skill of 2/3, so 2/3 of the dice rolls will strike the target. Then those successful 2/3 of the attack roll will be used on the 1/2 successes second roll(penetration).

So the elf chooses to make 10 attacks. After attacking, he finds out he only 3 of his attacks wounded the elf. How many should Model 1 have used to destroy the elf?


After figuring on the Excel formula, I need to add 3 different abilities/talents show in increased probability, the range of attackers could have. for example, the attacker may be able to reroll the results that are 1s on the attack roll can be rolled for a special "retry".

 

[MGrant]

Hi Jeff, I understand a bit of your expression.

I'm not sure how many dice calculation systems you've explored.

Sometimes there are challenges to achieve. I think this is a fun challenge.

Most tabletop games use 1d6 as a simple rolling system because manufacturers have low cost access to dice materials. The limit is the probabilities. For example this is a 2d6 chart below. Some games use this idea for "fear" tests. Lets say a troop of soldiers was hurt really bad and now they want to run.

In this example, ( unrelated to the one I'm presenting above) a good number to have would high, so the amount of chances of success are high.
The chart shows the potential chances that 2d6 can have. from double 1 to double 6. Obviously, double 6 would account for all the successes. And Double 1 would account for a fail. If my skill of moral was 10, then any number between 10 and 2 would be a success. ( 1 can never be rolled because its a total on 2 dice). When calculating the probablity of success we can say that






The cumulative success looks like this.

If one has a personal value score of 9 and any number of between 2-9 are successes, there is a 83% chance of success.



This is where Im getting to the 1/3 /1/2 and 2/3 numbers as successes.

When looking at 1d roll success, one must have a target. That target number often has a rating, between 2-6 (often in these types of games, rolling a 1 always is a miss, so 1/6 of the time will result in a miss). Again not relevant to the equation and Excel formula, but important information why there is never 100% in these dice rolls for these games.




So, When I say 3,4,5,6 are successes, means that 2/3 of the dice rolls will be successes. This is specific. 6 is always a hit, 1 is always a miss. And sill is incrmental, so 5,6 would be low skill because it will only hit 1/3 of the time. 3-6 would be good skill because 2/3 of the time will lead to success. The chart above has the reverse in the cumulative example.

So when one needs to the roll the target "2" in this game, that means 83% chance of success because 2,3,4,5,6 are all successes. 83% is as high as it goes. But some models have a special ability to reroll 1s. Which adds an additional split chance on that die to try for a success. So that 16% fail rate is reduce by their skill level to try again. If my skill is 2/3, then that reroll will effect that one chance die roll by 66.6% additional. I dont know how to add that yet, so I didnt bring this up above.

1d6 Roll
1 16.6%
2 83.3%
3 66.6 %
4 50 %
5 33.3 %
6 16% ( would be very hard to achieve If I needed a 6 to hit a target because 5-1 all miss vs. a high armored target)


It may not be clear, but the models have a armor rating. Some allow 50% of the damage through. Some allow 1/6. For sake of discussion, I really just need to get the formula and I can figure out what % to put in the field.

 

[MGrant]

Attacker rolls 10 dice ( arbitrary number guessing it will be enough to kill the 11 life target. please ignore that it';s impossible to get 11 damage with 10 dice. It's just the number chosen because the player didn't know initially how much life the target had.)



Rolls 10 dice and these are the dice results.

4 - 1s ( 1s always miss ) ( an unlucky roll! )
0 - 2s ( would have been misses because 1 and 2 miss )
3 - 3s ( success )
1 - 4s ( success )
0 - 5s ( success but none turned up on the roll )
2 - 6s ( success )

This rolling shows that even though the average of 6 hits was still rolled from 10 dice, the dynamic of dice rolling makes the game interesting.

Rolling the damage pentration using the successful hits:



2 and 3 miss.
4,5,6 will wound the model, because the game says, so. (IF the model had weaker armor, the test might be 3,4,5,6 will be successes for the enemy. It would never be 1,2,3,4. 6 is a/ways a success in this game mechanic, so it as the skill decreases, the numbers will lead to 6.) Players don't get to pick which numbers are successes. Players get to roll the dice. Sometimes all the dice might hit because they roll all 5,6s for example.

The skill that is worth knowing to make players good, is guessing the number of attack dice needed to attack a known target. In this example, we know we hit on 3,4,5,6 and damage the model on 50%. and the target has 11 life.

Back to the original question :
What is the minimum of dice needed to be rolled at one time to take out all 11 life of this target, based on 2/3 chance to hit and 1/2 chance to wound with those hit successes.

 

[Romsek]

I have an answer for you but I don't think you can implement it directly in Excel.
It involves a function they don't seem to support.

The probability that a player with $\displaystyle k$ hitpoints will be killed when
$\displaystyle P[\text{a hit through armor according to the scheme you have}] = p$
when $\displaystyle n$ dice are rolled is given by

[MATH]deth(n,k,p) = 1-p^{k+1} \binom{n+1}{k} (1-p)^{-k+n+1} \, _2F_1(1,n+2;-k+n+2;1-p)[/MATH]
Here $\displaystyle p=p_H \cdot p_A = \dfrac 2 3 \dfrac 1 2 = \dfrac 1 3$

It's the $\displaystyle _2F_1()$ that Excel doesn't provide.
Mathematica provides it. Wolfram Alpha probably provides it.
If you're happy with 11 hp I could make you a graph.

 

[Romsek]

I have an answer for you but I don't think you can implement it directly in Excel.

This doesn't seem to be matching up with sim. Ignore this for now.

 

[MGrant]

I deeply appreciate a response. Thank you kindly to your patience to read through my explanation. I understand it's not conceptually easy to visualize.

That chart is very interesting as it shows the curve of possibility. I like that a lot and I would like to understand it because it will show different success challenges.

I think I've narrowed down my question to its simplest form for everyone.

This is what I have in Excel taking my 10 att and the resulting value to the target. Now dice can't roll fractions, so we expect it to be 3 dice will be a success.

Very simple.

ATT * Hit * Pen = Dam
10 * 0.667 * 0.5 = 3.35

The need the equation for this problem is reverse. I know the targets damage it take. I want to remove that model at one time, so I need to know how many attacks I will need. Att = n

ATT * Hit * Pen = Dam
n * 0.667 * 0.5 = 11

How do I get this into Excel ?