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

 

[Fluff]

You can use the Averageif function for that

AVERAGEIF Function

The Excel AVERAGEIF function calculates the average of numbers in a range that meet supplied criteria. AVERAGEIF criteria can include logical operators (>,

exceljet.net

 

[Krabben]

You can use the Averageif function for that

AVERAGEIF Function

The Excel AVERAGEIF function calculates the average of numbers in a range that meet supplied criteria. AVERAGEIF criteria can include logical operators (>,

exceljet.net

Hey Fluff

My averageif function seems to include layers of the same column number, even if they are not in the same layer.

Any tips how to avoid that?

 

[Fluff]

What is your formula?

 

[RoryA]

Since you appear to have the same number repeating in column B but for different 'layers' you could do something like this:

Book1
	A	B	C
1	Value	Band	Average
2	5	9	5.2
3	3	9	5.2
4	6	9	5.2
5	4	9	5.2
6	8	9	5.2
7	1	5	2.7
8	2	5	2.7
9	5	5	2.7
10	6	6	5.0
11	4	6	5.0
12	3	5	5.5
13	8	5	5.5
14	7	6	4.4
15	6	6	4.4
16	5	6	4.4
17	3	6	4.4
18	1	6	4.4
19	0	6	4.4
20	8	6	4.4
21	1	6	4.4
22	3	6	4.4
23	5	6	4.4
24	9	6	4.4
Sheet1

Cell Formulas
Range	Formula
C2:C24	C2	=IF(B2=B1,C1,AVERAGE(A2:INDEX(A2:A$25,MATCH(TRUE,B2:B$25<>B2,0)-1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Krabben]

What is your formula?

=AVERAGEIF($B$1:$B$23,"="&B1,$A$1:A1)

 

[Fluff]

I hadn't noticed that the values in B repeat, but should be treated separately.
Try Rory's formula.

 

[Krabben]

Since you appear to have the same number repeating in column B but for different 'layers' you could do something like this:

Book1
	A	B	C
1	Value	Band	Average
2	5	9	5.2
3	3	9	5.2
4	6	9	5.2
5	4	9	5.2
6	8	9	5.2
7	1	5	2.7
8	2	5	2.7
9	5	5	2.7
10	6	6	5.0
11	4	6	5.0
12	3	5	5.5
13	8	5	5.5
14	7	6	4.4
15	6	6	4.4
16	5	6	4.4
17	3	6	4.4
18	1	6	4.4
19	0	6	4.4
20	8	6	4.4
21	1	6	4.4
22	3	6	4.4
23	5	6	4.4
24	9	6	4.4
Sheet1

Cell Formulas
Range	Formula
C2:C24	C2	=IF(B2=B1,C1,AVERAGE(A2:INDEX(A2:A$25,MATCH(TRUE,B2:B$25<>B2,0)-1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you Rory, that did the trick

 

[RoryA]

Glad we could help.

 

[Krabben]

Glad we could help.

Hey Rory, sorry to bother you again. Thought you had solved my problem :/

So the code you provided works great, if, the "band" number changes. If it doesn't, it returns #I/T.

Im looking for the average in a predefined range (eg. A4-A50), and when i drag it down, sometimes the band matches all through the array.

Thanks again