I had a request to explain the formula from post 5:
=LET(s,SEQUENCE(15),c,CHOOSE(s,B2-A2,C2-B2,C2-A2,D2-C2,D2-B2,D2-A2,E2-D2,E2-C2,E2-B2,E2-A2,F2-E2,F2-D2,F2-C2,F2-B2,F2-A2),sa,SEQUENCE(F2),sb,SEQUENCE(F2+1),f,FREQUENCY(c,sa),TEXTJOIN(", ",1,IF(f=0,"",sb&":"&f)))
First, a word about LET. LET is a new function that allows you to define variables within a formula for re-use or clarity. See here:
How to use the Excel LET function to Assign variables inside formula.
exceljet.net
So in this formula, I defined 5 variables, s, c, sa, sb, and f. Each variable has 2 parts, the name and the definition, so s is the name and SEQUENCE(15) is the definition. Another nice feature is that once you define a variable, you can use it while defining subsequent variables. Notice how when I define c I use the s variable. Then the final parameter in LET is the actual formula to output.
So given that, here's how the formula works. I define a sequence (s) from 1-15. Why 15? Because with 6 numbers there are 15 differences. These are enumerated in the CHOOSE in the formula. With 2 numbers there is 1 difference, with 3 there are 3 differences, with 4 there are 6, with 5 there are 10, with 6 there are 15, with 7 there are 21, etc. For math nerds, these are the triangular numbers.
Next, I need to get all of these differences in a single array. I tried several very clever ways to do that without having to list them all, and none of them worked. So I went with CHOOSE. CHOOSE lets you pick a option from a list based on an index. CHOOSE(2,"A","B","C") would return "B" since the index is 2 and the second choice is "B". If you give CHOOSE an array of indexes, it will return an array of choices. So CHOOSE({3,1},"A","B","C") would return {"C","A"}. So using the array s from the previous step, I give that to CHOOSE and I get an array (c) of all the differences.
Now we want to find out how many times each differences occurs. The FREQUENCY formula does exactly that. You give it an array of values, then another array of "buckets", and it returns an array of how many of the values fall into each bucket. (Google "Excel FREQUENCY function" for more detailed explanations.) I have the array of values, now I need the array of buckets. Since the numbers in this problem are in sorted order, small to large, the rightmost value is the largest value. I create another array (sa) that contains each of the numbers from 1 to the max value. Now we create the output array (f).
I create the sb array which is 1 bigger than the sa array since the output array from FREQUENCY is 1 bigger than the input bucket array. The extra element is to hold the count of values from the value array that don't fall into any of the buckets (which should not happen here, but we still need to allow for it).
So now we have all the information we need in f. We just need a way to present it. The other variations of the formula use the same array and present the results in different ways. You can probably figure them out by this point. But in this version I just use:
TEXTJOIN(", ",1,IF(f=0,"",sb&":"&f))
This checks each element in f, and if it's 0, that means there are no differences for that index number, so we return a "". If it's non-0, we return the index and the count, and TEXTJOIN puts them all together.
Hope this helps!
