Dynamic Average of top values in specific ranges

[santomax]

Hi everyone.

Im trying to find the average of the top values of several- non continued ranges. see image for reference:

What I need is being able to average the TOP value of range B4:B15 with the TOP (the ones in green) value of B17:28 and so on for the next following cells that are not in yellow. So basically doing an average of the max values found every 12 rows.

I had a formula that worked for me when my data was organized a little bit different (every 11 rows), but I need to follow this structure now.
Formula example: =LET(A,A3:A1904,B,B3:B1904,C,BYROW(WRAPROWS(FILTER(B,A=""),11),LAMBDA(x,MAX(x))),AVERAGE(FILTER(C,C<>0)))

Test Sample.xlsx
	A	B	C
1		test
2	Date	test	test/Hr
3	1/1/2023	522	87.0
4		138	26.4%
5		137	26.2%
6		72	13.8%
7		71	13.6%
8		64	12.3%
9		11	2.1%
10		29	5.6%
11			-
12			-
13			-
14			-
15			-
16	1/1/2023	246	49.2
17		39	15.9%
18		71	13.6%
19		86	16.5%
20		36	6.9%
21		13	2.5%
22		1	0.2%
23			-
24			-
25			-
26			-
27			-
28			-
29	1/1/2023	647	107.8
30		89	13.8%
31		107	20.5%
32		91	17.4%
33		20	3.8%
34		108	20.7%
35		130	24.9%
36		102	19.5%
37			-
38			-
39			-
40			-
41			-
42	1/1/2023	544	77.7
43		78	14.3%
44		117	22.4%
45		102	19.5%
46		56	10.7%
47		84	16.1%
48		37	7.1%
49		65	12.5%
50		5	1.0%
51			-
52			-
53			-
54			-
55	1/1/2023	574	95.7
56		78	13.6%
57		112	21.5%
58		114	21.8%
59		112	21.5%
60		95	18.2%
61		48	9.2%
62		15	2.9%
63			-
64			-
65			-
66			-
67			-
68	1/1/2023	680	85.0
69		74	10.9%
70		98	18.8%
71		106	20.3%
72		47	9.0%
73		120	23.0%
74		70	13.4%
75		103	19.7%
76		62	11.9%
77			-
78			-
79			-
80			-
81	1/1/2023	684	76.0
82		42	6.1%
83		64	12.3%
84		76	14.6%
85		62	11.9%
86		126	24.1%
87		93	17.8%
88		75	14.4%
89		95	18.2%
90		51	9.8%
91			-
92			-
93			-
94	1/1/2023	410	82.0
95		98	23.9%
96		103	19.7%
97		86	16.5%
98		55	10.5%
99		64	12.3%
100		4	0.8%
101			-
102			-
103			-
104			-
105			-
106			-
107	1/1/2023	489	97.8
108		142	29.0%
109		132	25.3%
110		80	15.3%
111		102	19.5%
112		32	6.1%
113		1	0.2%
114			-
115			-
116			-
117			-
118			-
119			-
120	1/1/2023	578	96.3
121		63	10.9%
122		106	20.3%
123		98	18.8%
124		75	14.4%
125		98	18.8%
126		118	22.6%
127		20	3.8%
128			-
129			-
130			-
131			-
132			-
133	1/1/2023	1021	102.1
134		116	11.4%
135		116	22.2%
136		108	20.7%
137		60	11.5%
138		96	18.4%
139		96	18.4%
140		129	24.7%
141		164	31.4%
142		80	15.3%
143		56	10.7%
144			-
145			-
Test

Cell Formulas
Range	Formula
B3,B133,B120,B107,B94,B81,B68,B55,B42,B29,B16	B3	=IF(SUM(B4:B15)=0,"-",(SUM(B4:B15)))
C3,C16,C29,C42,C55,C68,C81,C94,C107,C120,C133	C3	=IF(ISERROR((B3/(COUNTIF(B4:B15,">="&B3*0.05)))),"-",((B3/(COUNTIF(B4:B15,">="&B3*0.05)))))
C4,C17,C30,C43,C56,C69,C82,C95,C108,C121,C134	C4	=IF(B4="","-",(B4*100)/B3)
C5:C15,C18:C28,C31:C41,C44:C54,C57:C67,C70:C80,C83:C93,C96:C106,C109:C119,C122:C132,C135:C145	C5	=IF(B5="","-",(B5*100)/B$3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B134	Cell Value	top 1 values	text	YES
B135:B143	Cell Value	top 1 values	text	YES
B121:B127	Cell Value	top 1 values	text	YES
B108:B113	Cell Value	top 1 values	text	YES
B95:B100	Cell Value	top 1 values	text	YES
B82:B90	Cell Value	top 1 values	text	YES
B69:B76	Cell Value	top 1 values	text	YES
B56:B63	Cell Value	top 1 values	text	YES
B43:B50	Cell Value	top 1 values	text	YES
B30:B37	Cell Value	top 1 values	text	YES
B17:B23	Cell Value	top 1 values	text	YES
B4:B12	Cell Value	top 1 values	text	YES
B144:B145	Cell Value	top 1 values	text	YES
B128	Cell Value	top 1 values	text	YES
B129:B132	Cell Value	top 1 values	text	YES
B114:B115	Cell Value	top 1 values	text	YES
B116:B119	Cell Value	top 1 values	text	YES
B101:B102	Cell Value	top 1 values	text	YES
B103:B106	Cell Value	top 1 values	text	YES
B91:B93	Cell Value	top 1 values	text	YES
B77:B80	Cell Value	top 1 values	text	YES
B64:B67	Cell Value	top 1 values	text	YES
B51:B54	Cell Value	top 1 values	text	YES
B38:B41	Cell Value	top 1 values	text	YES
B24	Cell Value	top 1 values	text	YES
B25:B28	Cell Value	top 1 values	text	YES
B13:B15	Cell Value	top 1 values	text	YES

Im kinda lost on how to do what im looking for. I have tried just modding my original formula but I kinda gave up. If you guys have any ideas, they are all welcome.

 

[Fluff]

How about

=LET(A,A3:A1904,B,B3:B1904,C,BYROW(WRAPROWS(FILTER(B,A=""),12,""),LAMBDA(x,MAX(x))),AVERAGE(FILTER(C,C<>0)))

 

[santomax]

How about

=LET(A,A3:A1904,B,B3:B1904,C,BYROW(WRAPROWS(FILTER(B,A=""),12,""),LAMBDA(x,MAX(x))),AVERAGE(FILTER(C,C<>0)))

I cannot believe I was just missing the "" xD
Thank you so, it works just fine with the 12 instead of 11

 

[Fluff]

You're welcome & thanks for the feedback.