I am creating a formula that can take an array of items as an input and then output a sub-array of those items. The sub-array cannot have repeat items and must be at least 1 cell in length and at most x cells in length (where x = array size). The size of the sub-array is incrementally weighted towards smaller sizes. Once the size is chosen, it is equally probable that any set of items is included in the sub-array. Example
Array: bear, cat, dinosaur, dog, dolphin, zebra
Sub-array size(weight): 1(6), 2(5), 3(4), 4(3), 5(2), 6(1)
Sub-array size p = weight / sum weights (e.g. p(size=3) = 4 / (1+2+3+4+5+6) = ~19%)
Given a size of 5, there should be an equal chance that any five animals are included.
The issue here is that if you recalculate the sheet enough times, sometimes the LET() formula returns a #SPILL! error even though the 'Evaluate Formula' feature clearly shows that my final 'sorted' arguement in LET() returns a valid array. What gives? Thank you!