Forum Discussion
Count how many pairs of dice sum >= 7?
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! 😉
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).
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 djclementsThis appears to be an A.I. generated formula, as it is riddled with errors. As you've already pointed out, COUNTIF does not accept an array in its range argument. Aside from that, the use of SEQUENCE is also incorrect: SEQUENCE(n,n,1,1) is wrong for the first sequence, and TRANSPOSE is not needed for the second sequence.
A simplified formula for this scenario could be:
=SUM(--(SEQUENCE(6)+SEQUENCE(,6)>=7))/6^2For Office 2010:
=SUMPRODUCT(--(ROW(1:6)+COLUMN(A:F)>=7))/6^2However, if you wanted to scale up using more dice, this simple 2D matrix will not cut it.
With MS365, you could use a custom LAMBDA function defined in Name Manager as follows:
Name: PERMλ
Refers to:
=LAMBDA(number,number_chosen,MOD(TRUNC(SEQUENCE(number^number_chosen,,0)/number^(number_chosen-SEQUENCE(,number_chosen))),number)+1)Then, to return all possible permutations with 3 dice, for example:
=PERMλ(6,3)And, to calculate the probability that their sum is greater than or equal to 7:
=LET(a,PERMλ(6,3),SUM(--(BYROW(a,SUM)>=7))/ROWS(a))Note: Excel has a hard output limit of 1,048,576 rows, so this will only work for up to 7 dice, e.g. =PERMλ(6,7).
Cheers!
Have just tried this after reading yours and it makes sense now - apologies for my oversight. Is there still not a way to do it using lambda with a sequence and a transposed sequence?
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! 😉