Forum Discussion
JoeUser2004
Jan 14, 2025Bronze Contributor
Count how many pairs of dice sum >= 7?
Please help me help someone in another forum.
The problem: if we toss a pair of dice (6-sided), what is the probability that their sum is 7 or more?
As I demonstrate below, the answer comes down to enumerating and counting the number of pairs with sum >= 7. Then, the probability is (#paired sums >= 7) / #pairs, where #pairs is #sides^2.
(The answer is 21/36 = 7/12.)
My question is: is there a formula that calculates #paired sums >= 7 without doing the enumeration manually or relying on VBA?
I'm guessing that we can use Excel 365 features such as LAMBDA etc. But I know nothing of those features.
And less importantly, for my benefit, is there an Excel 2010 formula, without relying on VBA?
My "manual" solution:
Absolutely. REDUCE can be used to iterate through the number of dice, adding the SEQUENCE of sides (vertical vector) to the previous transposed results using TOROW (horizontal vector). The lambda definition could be something like this:
=LAMBDA(num_of_sides,num_of_dice,operator,value, LET( n, SEQUENCE(num_of_sides), m, IF(num_of_dice = 1, n, REDUCE(n, SEQUENCE(num_of_dice - 1), LAMBDA(a,i, TOROW(a) + n))), t, CHOOSE( XMATCH(operator, {"<";"<=";"<>";"=";">";">="}), m < value, m <= value, m <> value, m = value, m > value, m >= value ), SUM(--t)/COUNTA(t) ) )
You could name it DICEPROBλ, for example, then use it as follows:
=DICEPROBλ(6,2,">=",7)
Or:
=DICEPROBλ(6,3,">",9)
Or:
=DICEPROBλ(6,7,"<",25)
And so on and so forth.
This function returns the probability. If you want it to return the count instead, simply remove /COUNTA(t) from the last line of the function definition.
Note: the PERMλ function was a generic permutation generator I had saved from a previous project. It works but is not the most efficient method for this scenario. Thanks for prompting me to rethink it! 😉
- Etizaz24Brass Contributor
Hello!
Yes, this can be done using LAMBDA. First you have to create a LAMBDA function that calculates the number of pairs where the sum is 7 or more. The formula would be:
=LAMBDA(n, COUNTIF(SEQUENCE(n, n, 1, 1) + TRANSPOSE(SEQUENCE(n, 1, 1, 1)), ">=7"))
- n is the number of sides on the dice
- SEQUENCE(n, n, 1, 1) generates all possible values for the first die
- TRANSPOSE(SEQUENCE(n, 1, 1, 1)) generates all possible values for the second die
- The sums of all pairs are evaluated with COUNTIF(..., ">=7")
After this you could create a Named Formula, this would let you use this LAMBDA function again. To do this you go to Formulas > Name Manager, click New and enter a name. So for example you could name it CountDicePairs7 – and you would refer to the formula above for this.
Now you would be able to use the LAMBDA function anywhere with the following.
=CountDicePairs7(6)
This would give you the number of pairs where the sum is 7 or more.
Finally to calculate the probability you would divide this by the total number of pairs (6^2).
- JoeUser2004Bronze Contributor
I intend to mark this as "solution", even though it does not seem to work for me.
Errata (no strikeout font in this forum. :sigh: ).... Apparently, it is not. Thanks Etizaz24 for following up.
I suspect it fails for me because (a) my only access to "Excel 365" (sort of) is Excel for the Web (onedrive.live.com), which might be too dumbed-down; and (b) I don't know enough about the new features to see what I might be doing wrong in applying what seem to be very good instructions.
But I suspect it works or can be made to work for someone who knows the "Excel 365" features.
More to the point: you are the only one who correctly understood what I was looking for, namely: a formula (or two :wink: ) that returns "the number of pairs where the sum is 7 or more" without any "visible" (*) enumeration.
My fault for not articulating my requirements more clearly. I should know better. :sigh:
-----
(*) Re: "visible" enumeration
I would expect the formula(s) to enumerate the combinations internally. I don't believe we can "count" the qualifying combinations any other way (e.g. a combinatorial or other math expression).
BTW, did you test your formula?
I did not think COUNTIF accepts an array as the first parameter, notwithstanding the description in the help page. For example, =countif({3,4,5,6,7}, ">=5") is treated as a syntax error in Excel for the Web.
- Etizaz24Brass Contributor
I did think this could work but I think I have misunderstood how COUNTIF handles arrays.
I will try the LAMBDA solution suggested by djclements
- Tomsmith1122Copper Contributor
To find how many pairs of dice have a sum greater than or equal to 7, we need to consider all possible outcomes when rolling two dice. Each die has 6 sides, so there are 6 × 6 = 36 possible outcomes.
Now, we need to count how many of these outcomes give a sum of 7 or greater. The possible sums and their corresponding pairs are:
- Sum = 7: (1, 6), (2, 5), (3, 4), (4, 3), (5, 2), (6, 1) — 6 outcomes
- Sum = 8: (2, 6), (3, 5), (4, 4), (5, 3), (6, 2) — 5 outcomes
- Sum = 9: (3, 6), (4, 5), (5, 4), (6, 3) — 4 outcomes
- Sum = 10: (4, 6), (5, 5), (6, 4) — 3 outcomes
- Sum = 11: (5, 6), (6, 5) — 2 outcomes
- Sum = 12: (6, 6) — 1 outcome
Now, adding them up:
6 (for sum 7) + 5 (for sum 8) + 4 (for sum 9) + 3 (for sum 10) + 2 (for sum 11) + 1 (for sum 12) = 21 outcomes.So, there are 21 pairs of dice that sum to 7 or greater.