Average function that stops counting if another argument is met

[Krabben]

Hello Mr. excel forum

I'm looking for a formula that can find the average that stops counting if another argument is met.

In the picture below, i've divided it into 5 different "layers".
In these layers, the average value of the numbers in the column A, should be given in column C, if the number in the column B matches eachother.

Hope you guys understand my question. Any ideas?


Thanks

 

[RoryA]

My formula included an extra row below the data set specifically so there would always be a change. (Note that the data I posted went down to row 24 but the formula included row 25.)

 

[Krabben]

My formula included an extra row below the data set specifically so there would always be a change. (Note that the data I posted went down to row 24 but the formula included row 25.)

Book1
	A	B	C	D
1	Depth (meter)	Value	Band	Average
2	1	5	9	5,2
3	2	3	9	5,2
4	3	6	9	5,2
5	4	4	9	5,2
6	5	8	9	5,2
7	6	1	5	2,7
8	7	2	5	2,7
9	8	5	5	2,7
10	9	6	6	5,0
11	10	4	6	5,0
12	11	3	5	5,5
13	12	8	5	5,5
14	13	7	6	#N/A
15	14	6	6	#N/A
16	15	5	6	#N/A
17	16	3	6	#N/A
18	17	1	6	#N/A
19	18	0	6	#N/A
20	19	8	6	#N/A
21	20	1	6	#N/A
22	21	3	6	#N/A
23	22	5	6	#N/A
24	23	9	6	#N/A
25	24	5	6	#N/A
26	25	5	6	#N/A
27	26	5	6	#N/A
28	27	5	6	#N/A
29	28	5	6	#N/A
30	29	5	6	#N/A
31	30	5	6	#N/A
32	31	5	6	#N/A
33	32	5	6	#N/A
34	33	5	6	#N/A
Sheet1

Cell Formulas
Range	Formula
D2:D34	D2	=IF(C2=C1,D1,AVERAGE(B2:INDEX(B2:B16,MATCH(TRUE,C2:C16<>C2,0)-1)))

So here i've updated my #1 post with a Depth column. My average function should always consider a depth of 15 meters and should consider the "band" groups in this range.
Therefor, when the formula gets dragged down to D14 and further, it results in #N/A.

 

[RoryA]

That is a completely different requirement since it's a rolling range, not a static one. On that basis, it would seem that the results in column D shouldn't be repeating any more, in which case, you could use:

=AVERAGE(B2:INDEX(B2:B16,IFERROR(MATCH(TRUE,C2:C16<>C2,0)-1,15)))