Best way to calculate multiple averages? (See sample mini-sheet)

[wrecclesham]

Book1
	B	C	D
2			Average
3	Apples	65
4	Apples	62
5	Apples	16	47.7
6	Oranges	17
7	Oranges	61	39.0
8	Peaches	17
9	Peaches	72
10	Peaches	24
11	Peaches	62	43.8
12	Tomatoes	96	96.0
Sheet1

Cell Formulas
Range	Formula
D5	D5	=AVERAGE(C3:C5)
D7	D7	=AVERAGE(C6:C7)
D11	D11	=AVERAGE(C8:C11)
D12	D12	=AVERAGE(C12)

I have a similar spreadsheet to the one above, containing several thousand rows of data.

How could I calculate the green column automatically?

 

[Legacy 143009]

=IF(COUNTIF(B$2:B3,B2)=COUNTIF(B$2:B2,B2),AVARAGEIFS($C$2:$C$12,$B$2:$B$12,B2),"")

 

[wrecclesham]

=IF(COUNTIF(B$2:B3,B2)=COUNTIF(B$2:B2,B2),AVARAGEIFS($C$2:$C$12,$B$2:$B$12,B2),"")

Sorry for the dumb question but where would I actually use that formula?

I get a "#NAME?" error when I paste it into a blank cell.

 

[jasonb75]

Flashbond's formula contained a typo (incorrect spelling of AVERAGEIFS). You could simply correct their formula, or use an alternative (of which there are many).

Book1
	B	C	D
2			Average
3	Apples	65
4	Apples	62
5	Apples	16	47.66667
6	Oranges	17
7	Oranges	61	39
8	Peaches	17
9	Peaches	72
10	Peaches	24
11	Peaches	62	43.75
12	Tomatoes	96	96
Sheet1

Cell Formulas
Range	Formula
D3:D12	D3	=IF(B3=B4,"",AVERAGEIFS(C$3:C3,B$3:B3,B3))

 

[Legacy 143009]

Sorry for the dumb question but where would I actually use that formula?

I get a "#NAME?" error when I paste it into a blank cell.

I misspelled AVARAGE. It must be AVERAGE.
Anyway, use @jasonb75 's solution. Much more practical. I am also so dumb sometimes.

 

[wrecclesham]

Flashbond's formula contained a typo (incorrect spelling of AVERAGEIFS). You could simply correct their formula, or use an alternative (of which there are many).

Book1
	B	C	D
2			Average
3	Apples	65
4	Apples	62
5	Apples	16	47.66667
6	Oranges	17
7	Oranges	61	39
8	Peaches	17
9	Peaches	72
10	Peaches	24
11	Peaches	62	43.75
12	Tomatoes	96	96
Sheet1

Cell Formulas
Range	Formula
D3:D12	D3	=IF(B3=B4,"",AVERAGEIFS(C$3:C3,B$3:B3,B3))

Thanks everyone. I didn't realize it was just a typo.

It works perfectly now, except I get a "#DIV/0!" error for the first group of values that are being averaged (should be 47.67 for Tomatoes).

Any idea how I can modify the code so it can handle the first item correctly?

 

[jasonb75]

Anyway, use @jasonb75 's solution. Much more practical. I am also so dumb sometimes.

We've all had days when you feel like that (I had a seriously dumb moment in a thread last night ).

@wrecclesham noting that you have 'several thousand' rows of real data, you might find that this method works more efficiently.
You will need to pay close attention to the position of the cells used in the formula and the $ symbols for the mix of absolute and relative references.
If you are using an up to date version of excel (it would help us if you added your excel version to your forum profile by clicking the 'Account Details' link in my signature at the bottom of this post) then I can make a dynamic version of it using the LET function for you so that you don't need to change it if you add more data.

Book1
	B	C	D
2			Average
3	Apples	65
4	Apples	62
5	Apples	16	47.66667
6	Oranges	17
7	Oranges	61	39
8	Peaches	17
9	Peaches	72
10	Peaches	24
11	Peaches	62	43.75
12	Tomatoes	96	96
Sheet1

Cell Formulas
Range	Formula
D3:D12	D3	=IF(B3=B4,"",AVERAGE(C3:INDEX($C$2:$C$12,IFERROR(MATCH(1E+100,D$2:D2)+1,1))))

 

[wrecclesham]

We've all had days when you feel like that (I had a seriously dumb moment in a thread last night ).

@wrecclesham noting that you have 'several thousand' rows of real data, you might find that this method works more efficiently.
You will need to pay close attention to the position of the cells used in the formula and the $ symbols for the mix of absolute and relative references.
If you are using an up to date version of excel (it would help us if you added your excel version to your forum profile by clicking the 'Account Details' link in my signature at the bottom of this post) then I can make a simple dynamic version of it using the LET function for you.

Book1
	B	C	D
2			Average
3	Apples	65
4	Apples	62
5	Apples	16	47.66667
6	Oranges	17
7	Oranges	61	39
8	Peaches	17
9	Peaches	72
10	Peaches	24
11	Peaches	62	43.75
12	Tomatoes	96	96
Sheet1

Cell Formulas
Range	Formula
D3:D12	D3	=IF(B3=B4,"",AVERAGE(C3:INDEX($C$2:$C$12,IFERROR(MATCH(1E+100,D$2:D2)+1,1))))

365 all day, every day!

 

[Dave Patton]

You could try Data Subtotal

T202211b.xlsm
	A	B
1
2	Product	Amount
6	Apples Average	47.67
9	Oranges Average	39.00
14	Peaches Average	43.75
16	Tomatoes Average	96.00
17	Grand Average	49.20
18
7b

Cell Formulas
Range	Formula
B6	B6	=SUBTOTAL(1,B3:B5)
B9	B9	=SUBTOTAL(1,B7:B8)
B14	B14	=SUBTOTAL(1,B10:B13)
B16	B16	=SUBTOTAL(1,B15:B15)
B17	B17	=SUBTOTAL(1,B3:B15)

 

[jasonb75]

I think that I was a little too slow in removing the word 'simple' from the offer of making it dynamic, the formula is much the same as the one in my last reply but you won't need to edit it if you add more data to the source, just fill down as needed.

Whilst the formula looks more complicated, it uses simpler and more efficient methods to process the data. You may, or may not notice a difference in calculation time for the formula when compare to one using averageifs depending on a number of factors.

One thing that I should point out is that all of the suggestions provided assume that (for example) all Apples will be in consecutive rows, if they are separated then each group of Apples will have its own average.

=IF(B3=B4,"",LET(l,MATCH("zzz",B:B),r,C$2:INDEX(C:C,l),AVERAGE(C3:INDEX(r,IFERROR(MATCH(1E+100,D$2:D2)+1,1)))))

B3=B4 should refer to the item of interest (e.g. Apples) in the same row as the formula, and the row below.
B:B should refer to the entire column of items.
C$2 should refer to the cell immediately above the first value in the column of values to average.
C:C should refer to the entire column of values.
C3 should refer to the value cell in the same row as the formula.
D$2:D2 should refer to the cell above the one where you enter the first formula before filling down.

Hopefully that all makes sense.