# Dice Probability for Rerolling Dice Spreadsheet Formula

How can I express the dice probability for rolling dice vs a target number with re-rolls?

There are a number of dice rolled to get a target number of hits.

• A "hit" is a 4, 5, or 6 on 1d6.
• Every 6 can be re-rolled to get another hit.
• There is a target number of hits required for success.

For example, if you rolled 4 dice with a target of 2 hits, I would expect the chance of success to be around 66%.

However, if you wanted to calculate the odds of getting 5 hits on 4 dice, you would need to know the odds of getting a 6 on any of those 4 die and then getting a 4,5,or 6 to get that additional required hit.

I have read several articles, such as Ed Collins site, but I cannot get it to work in a Google spreadsheet.

From that, I have gathered: 1 - (3/6)^4 for 4 dice and one hit.

For 4 dice with 2 hits, I tried multiplying both probabilities together: 1 - (3/6)^4 * (3/6)^4

I also tried subtracting the total number of not 4,5,6 and then subtracting that from the total number of combinations and dividing the probability from that.

But that does not come out as expected either.

For re-rolling a 6, I looked at this post, but do not understand it: probability involving rerolling dice

Is there a single Google formula that can represent the typical situation of rolling multiple dice vs a target number and then also the odds of re-rolling to achieve the success?

• Thanks, that helps a little. How do I find the standard probability first? This part is not working as expected 1 - (3/6)^4 * (3/6)^4. Do I do the method where I multiply probability? Or do I subtract total permutations from total possibilities? I'm stuck on that part first. – User970008 May 13 '19 at 15:28

For a single die, let's find the expected value for the number of hits.

You have a $$\dfrac{1}{2}$$ chance of not getting any hits on one die.

You have a $$\dfrac{2}{6} + \dfrac{1}{6}\cdot \dfrac{1}{2} = \dfrac{5}{12}$$ chance of getting exactly 1 hit on one die.

You have a $$\dfrac{1}{6}\cdot \dfrac{1}{3}+\dfrac{1}{6^2}\cdot \dfrac{1}{2} = \dfrac{5}{72}$$ chance of getting exactly 2 hits.

In general, for $$h>0$$, the probability of exactly $$h$$ hits is $$\dfrac{5}{2\cdot 6^h}$$

I am not going to go into the proof of this, but I will demonstrate that the total probability is preserved.

$$\sum_{h\ge 1} \dfrac{5}{2\cdot 6^h} = \dfrac{5}{12}\sum_{h\ge 0} \left(\dfrac{1}{6}\right)^h = \dfrac{5}{12}\cdot \dfrac{6}{5} = \dfrac{1}{2}$$

Plus the probability of $$h=0$$, which is $$\dfrac{1}{2}$$.

So, this gives the expected value:

$$\dfrac{5}{12}\sum_{k\ge 0}(k+1)\left(\dfrac{1}{6}\right)^k = \dfrac{3}{5}$$

So, that is the expected value for the number of hits.

Continuing, let's create some notation.

Let $$P(n,h)$$ be the probability of rolling $$n$$ dice and getting exactly $$h$$ hits. Let $$P(n,h^+)$$ be the probability of rolling $$n$$ dice and getting at least $$h$$ hits.

$$P(n,h) = \sum_{\sum_{i=1}^n a_i = h}\prod_{i=1}^n\begin{cases}\tfrac{1}{2}, & a_i = 0 \\ \tfrac{5}{2\cdot 6^{a_i}}, & a_i>0\end{cases}$$

Basically, you are summing over all nonnegative integer solutions to the Diophantine equation: $$a_1+\cdots + a_n = h$$

This tells you which dice rolled hits, and the total number of hits is $$h$$. Then, multiply the probabilities that the chosen die scores that many hits.

This is an impractical approach. Instead, we can try to break down the probabilities.

Example:

$$P(4,2^+) = 1-P(4,0)-P(4,1) = 1-\left(\dfrac{1}{2}\right)^4-\dbinom{4}{1}\left(\dfrac{5}{12}\right)\left(\dfrac{1}{2}\right)^3 \approx 72\%$$

What I am doing here is for $$P(4,0)$$, this is the probability that not a single die rolled a hit. For $$P(4,1)$$, this is the probability that exactly one die rolled exactly one hit.

$$P(4,5^+) = 1-P(4,0)-P(4,1)-P(4,2)-P(4,3)-P(4,4)$$

This is a bit trickier to calculate.

\begin{align*}P(4,4) & = \dbinom{4}{1}P(1,4)P(3,0)+\dbinom{4}{2}\dbinom{2}{1}P(1,3)P(1,1)P(2,0)+\dbinom{4}{2}P(1,2)^2P(2,0)+\dbinom{4}{3}\dbinom{3}{1}P(1,2)P(1,1)^2P(1,0)+P(1,1)^4 \\ & = 4\left(\dfrac{5}{2\cdot 6^4}\right)\left(\dfrac{1}{2}\right)^3+12\left(\dfrac{5}{2\cdot 6^3}\right)\left(\dfrac{5}{12}\right)\left(\dfrac{1}{2}\right)^2+6\left(\dfrac{5}{72}\right)^2\left(\dfrac{1}{2}\right)^2+12\left(\dfrac{5}{72}\right)\left(\dfrac{5}{12}\right)^2\left(\dfrac{1}{2}\right)+\left(\dfrac{5}{12}\right)^4 \\ & = \dfrac{865}{6912}\end{align*}

Essentially, what I am calculating is the probability of the following:

Only one die hits, but four times

2 dice have hits, one rolls 3 hits one rolls 1 hit

2 dice hit, both roll 2 hits

3 dice hit, one rolls 2 hits, two others roll 1 hit each

4 dice hit, one hit each

$$P(4,3) = \dbinom{4}{1}\left(\dfrac{5}{2\cdot 6^3}\right)\left(\dfrac{1}{2}\right)^3 + \dbinom{4}{2}\dbinom{2}{1}\left(\dfrac{5}{72}\right)\left(\dfrac{5}{12}\right)\left(\dfrac{1}{2}\right)^2+\dbinom{4}{3}\left(\dfrac{5}{12}\right)^3\left(\dfrac{1}{2}\right) = \dfrac{205}{864}$$

$$P(4,2) = \dbinom{4}{1}\left(\dfrac{5}{72}\right)\left(\dfrac{1}{2}\right)^3+\dbinom{4}{2}\left(\dfrac{5}{12}\right)^2\left(\dfrac{1}{2}\right)^2 = \dfrac{85}{288}$$

$$P(4,5^+) = \dfrac{55}{768}$$

Edit: How to build an Excel spreadsheet to perform these calculations:

On Sheet1 - Probability of getting exact number of hits:
Cell C1: Number of Dice
Cell C2: 0
Cell D2: 1
Cell E2: 2
.
.
.
Cell M2: 10 (go as high as you like)
.
.
.

Cell A3: Number of Hits
Cell B3: 0
Cell B4: 1
.
.
.
Cell B13: 10 (go as high as you like)
.
.
.

Cell C3: =POWER(0.5,C2)
Copy this cell and paste formula to all cells C3:M3

Set Cells C4:C13 to 0
Cell D4: =5/(2*POWER(6,B4))
Copy this cell and paste formula to all cells D4:D13

Cell E4: =E2*$D4*POWER(0.5,D2)) Copy this cell and paste formula to all cells E4:M4 Cell E5: =SUMPRODUCT($$D$$3:$$D5,N(OFFSET(D5,ROW(D$$3)-ROW(D$3:D5),0)))
Copy this cell and paste formula to all cells E5:M13

At this point, you should have a table filled out with all probabilities for getting the exact number of hits (0 to 10) on any number of dice (0 to 10).

Rename the spreadsheet: NumberHitsCalculator (or whatever else you want to name it, but I am using this name below).

Add a new worksheet. I called it ThresholdCalculator.
Copy everything from NumberHitsCalculator to get the same general layout. Change cell A3 to: Minimum Number of Hits

Change cell C3:M3 to be 1 across the board (you will always get at least zero hits).

Cell ThresholdCalculator!D4: =D3-NumberHitsCalculator!D3
Copy this cell and paste formula to all cells: ThresholdCalculator!D4:M13

This will give you all of the probabilities of hitting specific thresholds given the number of dice. Examples we have already gone over, like P(4,2+) gives 0.729167 as expected. P(4,5+) gives 0.071615 as expected, as well.

• This is very helpful. I have a lot of questions. First, if I want to look at rolling 4 dice to get 5 successes, I use P(4,5+). Is that correct? How do I begin to break that down into a formula for an Excel doc or Google Spreadsheet? – User970008 May 13 '19 at 17:21
• It is not straightforward like that. Excel would likely require a macro to calculate efficiently. Google spreadsheet would probably not work at all. I can probably write up a macro to do this. R would likely be the easiest language to write it, but it should work in Excel, as well. – InterstellarProbe May 13 '19 at 17:23
• Awesome. I just need it for Google spreadsheet. Thanks! This explanation is really fantastic. – User970008 May 13 '19 at 17:30
• I doubt that a Google spreadsheet can handle macros the same way Excel can. This really is not easy to calculate outside of a statistics language. I am not sure what I could do in Google Sheets. – InterstellarProbe May 13 '19 at 17:35
• OK, let's go with Excel and I'll see if I can translate that. So, for 4 dice with 1 hit, the probability is around 90% and 2 hits on 4 dice is around 66-68%, right? Then, as above, to get 5 hits on 4 dice, we are looking at 55/768 or 7%, right? – User970008 May 13 '19 at 17:46