Forum Discussion
Count how many pairs of dice sum >= 7?
- Jan 17, 2025
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! 😉
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.
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