average outcome of rolling 3 dice and taking the highest single result?

Question

So I saw a post on here from some years ago and after reading the comments (most of which are way over my head) bone of the comments gave me an equation I can put into excel to generate my desired outcome. =((B6+1)(4B6-1))/(6*B6) B6 is the number of faces on the dice, Dn. So could be a D6 or D4 or D20 or whatever.

What is the average of rolling two dice and only taking the value of the higher dice roll?

Now, my question is 2 fold, is this correct? (it seems to be ok and give me an average) and how do I expand this to be 'average of rolling 3 dice and taking the highest single result'

Please note, I'm not strong on this stuff so complex equations I cant transpose into excel are going to go over my head

Much appreciated

Answer 1

Walking through the problem and restating might help conceptualize the problem.

Each die can roll any face with equal probability. So, if you were to roll $n$ dice, you can record that outcome as a list of $n$ numbers representing the faces rolled. The number of different possible outcomes is the product of the number of outcomes for each individual die. Example: if there are three six-sided dice, the number of possible outcomes is $6\times 6 \times 6 = 6^3=216$.

Now, let's determine outcomes of dice rolls that give a specific result. Suppose the highest number shown is $1$. Since $1$ is the lowest number of every die, this means no die can roll anything greater than $1$. If every die has only 1 number they can roll, there is only $1$ way that can occur.

Now, let's consider the highest rolled number is $k$ among all of the dice. This means that every die can roll any number from $1$ to $k$ and at least one die rolled $k$. If there are $n$ dice, the number of ways for all dice to roll no greater than $k$ (assuming every die has at least $k$ sides) is $k^n$. But, this includes the possibility that no die rolls $k$, which occurs when the highest rolled is no greater than $k-1$. So, the number of outcomes where the highest rolled die is exactly $k$ is $k^n-(k-1)^n$.

Suppose we have $n$ dice, each with $d$ sides. If we were to roll all of the dice and record only the highest number rolled, we can calculate this expected value as such:

$$\sum_{k=1}^d \dfrac{k(k^n-(k-1)^n)}{d^n}$$

To make this as Excel formula, try this:

$$\begin{align*}& \text{In cell A1, put "Number of dice"} \\ & \text{In cell A2, put "Number of sides"} \\ & \text{In cell A3, put "Expected maximum roll"} \\ & \text{In cell B1, put "3"} \\ & \text{In cell B2, put "6"} \\ & \text{In cell B3, put "=Sum(Row(Indirect("1:"&B2))*(Power(Row(Indirect("1:"&B2)),B1)-Power(Row(Indirect("1:"&B2))-1,B1))/Power(B2,B1))"}\end{align*}$$

When entering that formula, you need to turn it into an array formula using CTRL+SHIFT+ENTER.

Answer 2

If $X_1, X_2, X_3$ are the values from the three dice rolls, you're interested in the maximum $Y = \max (X_1, X_2, X_3)$ and it's average, or expected value $\mathbb{E}(Y)$.

The CDF of $Y$ is the probability that the maximum roll is less than or equal to a certain value, $$F_Y (y) = P(Y \leq y) = P(X_1 \leq y)P(X_2 \leq y)P(X_3 \leq y) = (F_{X_i} (y))^3$$ where $F_{X_i} (y)$ is the CDF of an individual roll.

$$F_{X_i} (y) = \frac{y}{6}$$

And so the PDF of $Y$ is $$f_Y (y) = F_Y (y) - F_Y (y-1) = \left( \frac{y}{6}\right)^3 - \left( \frac{y-1}{6}\right)^3$$

The expected value then just comes from evaluating this probability against each possible value,

$$\mathbb{E}(Y) = \left(0 \cdot \frac{1}{6^3}\right)+\sum_{y=1}^6 y \cdot \left( \left( \frac{y}{6}\right)^3 - \left( \frac{y-1}{6}\right)^3 \right) = \frac{119}{24}$$

Answer 3

Since Excel is Turing-complete, you can just have it essentially go through the direct computation, as given in SlipEternal's answer. I did, however, want to tie things back to the two-dice example you gave in your original question.

I don't think there's a general closed formula for the maximum of $n$ dice, each with $d$ sides that you can code in Excel. There is a general closed formula, but it uses the generalized harmonic numbers:

\begin{align} E(\text{max}_{n, d}) & = \frac{1}{d^n} \sum_{k=1}^d k [k^n - (k-1)^n] \\ & = \frac{1}{d^n} \sum_{k=1}^d [k^{n+1} - (k-1)^{n+1} - (k-1)^n] \\ & = \frac{1}{d^n} \left[ H_d^{(-n-1)}-H_{d-1}^{(-n-1)}-H_{d-1}^{(-n)} \right] \end{align}

We can, however, come up with formulas that work for specific values of $n$ (the number of dice), for any number of sides. For example, for three dice, the formula is

$$ E(\text{max}_{3, d}) = \frac{(d+1)(3d-1)}{4d} $$

which for $d = 6$ (regular d$6$'s) yields $\frac{119}{24} \approx 4.96$. Other expressions are

$$ E(\text{max}_{2, d}) = \frac{(d+1)(4d-1)}{6d} $$

$$ E(\text{max}_{4, d}) = \frac{(d+1)(24d^3-9d^2-d+1)}{30d^3} $$

$$ E(\text{max}_{5, d}) = \frac{(d+1)(10d^3-4d^2-d+1)}{12d^3} $$

For $d = 2, 3, 4, 5$, these yield, respectively, $\frac{161}{36} \approx 4.47, \frac{119}{24} \approx 4.96, \frac{6797}{1296} \approx 5.24, \frac{14077}{2592} \approx 5.43$.