Average max of every nth data set

[santomax]

Hi there

I have to get the average of the max of given data sets, skipping a yellow row in between. So for example Calculate the max of B4:B13 and the skip B14, to then calculate the max of B15:B24 and so on and so forth. to at the end average all of the max values calculated. I have been trying with array formulas and using MOD row functions, but I only make it work for single cell values, not for "data set" ranged to select the MAX (an example I made to calculate the average of every YELLOW row skipping 11 rows was =AVERAGE(IF(MOD(ROW(B:B)-ROW(B3),11)=0,IF(B:B<>"",B:B))).

Some help would be very appreciated!

 

[StephenCrump]

Welcome to the Forum!

With Excel 365, you could do it along these lines:

	A	B	C	D	E
1		Groups	4
2		Length	5
3		Gap	1
4
5		1		Result	12.25
6		2		CHECK	12.25
7		3
8		4
9		5
10
11		9
12		8
13		7
14		6
15		5
16
17		12
18		13
19		11
20		10
21		9
22
23		19
24		20
25		21
26		22
27		21
28
Sheet1

Cell Formulas
Range	Formula
E5	E5	=LET(start,B5,grps,C1,L,C2,G,C3,d,OFFSET(start,,,grps*(L+G)),AVERAGE(BYROW(INDEX(d,SEQUENCE(grps,,0,L+G)+SEQUENCE(,L)),LAMBDA(r,MAX(r)))))
E6	E6	=AVERAGE(MAX(B5:B9),MAX(B11:B15),MAX(B17:B21),MAX(B23:B27))

 

[santomax]

Result​	12.25​
6	2​	CHECK​	12.25​
7	3​

Welcome to the Forum!

With Excel 365, you could do it along these lines:

	A	B	C	D	E
1		Groups	4
2		Length	5
3		Gap	1
4
5		1		Result	12.25
6		2		CHECK	12.25
7		3
8		4
9		5
10
11		9
12		8
13		7
14		6
15		5
16
17		12
18		13
19		11
20		10
21		9
22
23		19
24		20
25		21
26		22
27		21
28
Sheet1

Cell Formulas
Range	Formula
E5	E5	=LET(start,B5,grps,C1,L,C2,G,C3,d,OFFSET(start,,,grps*(L+G)),AVERAGE(BYROW(INDEX(d,SEQUENCE(grps,,0,L+G)+SEQUENCE(,L)),LAMBDA(r,MAX(r)))))
E6	E6	=AVERAGE(MAX(B5:B9),MAX(B11:B15),MAX(B17:B21),MAX(B23:B27))

HI.
This formula works wonders! the only problem that I have, is that the grp number is a limited fixed variable, I would like to be able to make it like a dynamic range that if add new data, it recalculates w/o needed to update the group number, and also because if the group number will calculate the average of empty cells, diving the average by an empty value; i.e If I have 4 groups, two blank and tow with a MAX of 20, the average comes up to 10.
I know that Im asking for a lot, but I do really appreciate the formula you created, totally our of my league xD

 

[StephenCrump]

Will this be more suitable:

	A	B	C	D	E
1		Groups	4
2		Length	5
3		Gap	1
4
5		1		Result	12
6		2		CHECK	12
7		3
8		4
9		5
10
11		9
12		8
13		7
14		6
15		5
16
17
18
19
20
21
22
23		19
24		20
25		21
26		22
27		21
28
Sheet1

Cell Formulas
Range	Formula
C1	C1	=CEILING((LOOKUP(2,1/ISNUMBER(B:B),ROW(B:B))-ROW(B5)+1)/(C2+C3),1)
E5	E5	=LET(start,B5,grps,C1,L,C2,G,C3,d,OFFSET(start,,,grps*(L+G)),AVERAGE(BYROW(INDEX(d,SEQUENCE(grps,,0,L+G)+SEQUENCE(,L)),LAMBDA(r,IF(COUNT(r),MAX(r))))))
E6	E6	=AVERAGE(MAX(B5:B9),MAX(B11:B15),MAX(B23:B27))