FREQUENCY_BINS

[tboulden]

Excel function FREQUENCY(data_array,bins_array) returns an array of counts of numbers in data_array based on numeric ranges determined by bins_array.

FREQUENCY_BINS filters data_array consecutively based on the bin criteria and returns a row for each bin.

=LAMBDA(data_array,bins_array,
  LET(
    pair,
      LAMBDA(pass,keep,
        LAMBDA(sw,
          SWITCH(sw,
            "passed",pass,
            "kept",keep
          )
        )
      ),
    vFlat_data,REDUCE(,data_array,vStack),
    hFlat_bins,REDUCE(,bins_array,hStack),
    sorted_bins,SORT(hFlat_bins,,1,TRUE),
    sorted_idx,
      hstack(
        SORTBY(SEQUENCE(,COLUMNS(hFlat_bins)),hFlat_bins,1),
        COLUMNS(hFlat_bins)+1),
    max_pred,isGreaterThan(MAX(hFlat_bins)),
    preds,hstack(MAP(sorted_bins,isLessThanOrEqualTo),max_pred),
    pairs,
      SCAN(pair(vFlat_data,),preds,
        LAMBDA(acc,pred,
          LET(
            vals,acc("passed"),
            passed,FILTER(vals,NOT(pred(vals)),""),
            kept,FILTER(vals,pred(vals),""),
            pair(passed,kept)
          )
        )
      ),
    binned_data,
      REDUCE(,pairs,
        LAMBDA(acc,val,
          hStack(
            IFERROR(acc("kept"),acc),
            val("kept")
          )
        )
      ),
    final,
      TRANSPOSE(SORTBY(binned_data,sorted_idx,1)),
    final
  )
)

Uses these helper functions:

isLessThanOrEqualTo

=LAMBDA(val,
  LAMBDA(vals,
    vals<=val
  )
)

isGreaterThan

=LAMBDA(val,
  LAMBDA(vals,
    vals>val
  )
)

LAMBDA_bins.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1				bins_array								data_array								bins_array
2				3	8	13						1	2	3	4					-96	-77.4	-67.4	-46.6	-6.8	13	25	44.5	67.2	87
3												5	6	7	8
4	FREQUENCY	Check?		frequency_bins								9	10	11	12		FREQUENCY	Check?	frequency_bins
5	3	3		1	2	3						13	14	15	16		1	1		-96
6	5	5		4	5	6	7	8									7	7		-88	-81	-95	-89	-86	-81	-87
7	5	5		9	10	11	12	13				data_array					8	8		-77	-75	-71	-73	-69	-76	-72	-76
8	3	3		14	15	16						80	87	-88	-2		8	8		-54	-55	-56	-67	-48	-60	-48	-51
9												57	73	84	-54		8	8		-8	-19	-8	-23	-29	-22	-11	-46
10				bins_array								21	-81	41	-55		10	10		-2	-1	-5	5	13	7	13	13	-6	1
11				2	6	10	14					-1	43	-8	-56		7	7		21	25	25	19	21	15	16
12												-95	89	25	-19		7	7		41	43	32	38	43	39	38
13	FREQUENCY	Check?	frequency_bins									58	-67	68	-48		8	8		57	58	55	59	60	67	50	48
14	2	2		1	2							96	-8	-89	-86		9	9		80	87	73	84	68	83	87	83	83
15	4	4		3	4	5	6					32	38	-77	-81		7	7		89	96	88	91	97	95	100
16	4	4		7	8	9	10					-75	55	25	19
17	4	4		11	12	13	14					-23	88	21	43					bins_array
18	2	2		15	16							91	83	-60	-71					100	80	60	40	20	0	-20	-40	-60	-80
19												-73	-5	-29	5
20				bins_array								59	-69	-76	-22		FREQUENCY	Check?	frequency_bins
21				5	20	30	10	40	15			13	-48	60	97		13	13		87	84	89	96	88	91	83	97	95	100	87	83	83
22												-11	95	-51	67		4	4		80	73	68	67
23	FREQUENCY	Check?	frequency_bins									7	-72	50	48		10	10		57	41	43	58	55	43	59	60	50	48
24	5	5		1	2	3	4	5				-76	100	87	13		8	8		21	25	32	38	25	21	39	38
25	1	1		16								-46	39	-96	83		9	9		19	5	13	7	13	15	13	1	16
26	0	0										15	13	38	-6		8	8		-2	-1	-8	-19	-8	-5	-11	-6
27	5	5		6	7	8	9	10				1	83	16	-87		3	3		-23	-29	-22
28	0	0															7	7		-54	-55	-56	-48	-48	-51	-46
29	5	5		11	12	13	14	15					###				10	10		-67	-77	-75	-60	-71	-73	-69	-76	-72	-76
30	0	0															8	8		-88	-81	-95	-89	-86	-81	-96	-87
31																	0	0
frequency_bins

Cell Formulas
Range	Formula
D2:F2	D2	={3,8,13}
L2:O5	L2	=SEQUENCE(4,4)
T2:AC2	T2	=PERCENTILE.INC(L8#,SEQUENCE(,10,0,0.1))
A5:A8,A24:A30,A14:A18	A5	=FREQUENCY($L$2#,D2#)
B5:B8,B24:B30,R21:R31,B14:B18,R5:R15	B5	=BYROW(D5#,LAMBDA(row_,COUNT(row_)))
D5:H8,D24:H30,D14:G18	D5	=frequency_bins($L$2#,D2#)
Q5:Q15,Q21:Q31	Q5	=FREQUENCY($L$8#,T2#)
T5:AC15,T21:AF31	T5	=frequency_bins($L$8#,T2#)
L8:O27	L8	={80,87,-88,-2;57,73,84,-54;21,-81,41,-55;-1,43,-8,-56;-95,89,25,-19;58,-67,68,-48;96,-8,-89,-86;32,38,-77,-81;-75,55,25,19;-23,88,21,43;91,83,-60,-71;-73,-5,-29,5;59,-69,-76,-22;13,-48,60,97;-11,95,-51,67;7,-72,50,48;-76,100,87,13;-46,39,-96,83;15,13,38,-6;1,83,16,-87}
D11:G11	D11	={2,6,10,14}
T18:AC18	T18	=SEQUENCE(,10,100,-20)
D21:I21	D21	={5,20,30,10,40,15}
M29	M29	=FREQUENCY
Dynamic array formulas.