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?

 

[Dave Patton]

The data in the spreadsheet looks better.
1. concept works with multiple columns such as subjects
2. you can copy the visible data and sort on that range
Select the cells that you want to copy
Click Home > Find & Select, and pick Go To Special
.Click Visible cells only > OK.
Click Copy or press Ctrl+C.
Paste at relevant location
Sort

T202211b.xlsm
	A	B	C	D
1
2		Name	Math	English
6		Name 1 Avg	47.7	79.0
9		Name 2 Avg	39.0	62.5
14		Name 3 Avg	43.8	77.5
16		Name 4 Avg	96.0	90.0
17		Grand Avg	49.2	76.2
18
19
20		Sorted descending on Math
21		Name	Math	English
22		Name 4 Avg	96.0	90.0
23		Name 1 Avg	47.7	79.0
24		Name 3 Avg	43.8	77.5
25		Name 2 Avg	39.0	62.5
26
7bb

Cell Formulas
Range	Formula
C6:D6	C6	=SUBTOTAL(1,C3:C5)
C9:D9	C9	=SUBTOTAL(1,C7:C8)
C14:D14	C14	=SUBTOTAL(1,C10:C13)
C16:D16	C16	=SUBTOTAL(1,C15:C15)
C17:D17	C17	=SUBTOTAL(1,C3:C15)

 

[Dave Patton]

You could also calculate the averages and prepare the report (sorted from highest to lowest) with one formula using Excel 365.

We do not know the location of the names and amounts with your data and we do not know if you want the report on the same sheet or another.

You could post 20 -30 rows that illustrate your data; the forum provides a tool named XL2BB to help with this process.

 

[Dave Patton]

I prepared an example of the formula that I mentioned in post # 22.
Notes
1. the data is not sorted
2. the report can be on the same sheet or on another sheet
3. the report can show just the top 5 or number that you specify ( I include an example but ignore this if it isn't applicable.
4. the named ranges are not necessary; they make the formula easier to read and I do not know the layout of a user's data
5. the formula is entered in one cell and the information spills right and down

An extract of the data follows and the result of the formula(s) is in the next post.

Averageifs.xlsm
	A	B	C	D	E
1		Name	Class	Subject	Grades
2		A			90%
3		A			89%
4		A			88%
5		A			91%
6		A-2			94%
7		A-2			97%
8		B			92%
9		B			92%
10		Z			80%
11		Z			75%
12		Z			75%
13		C			92%
14		C			92%
15		C			92%
16		Z			88%
17		D			99%
18		D			99%
19		E			65%
20		E			55%
21		E			55%
22		E			65%
23		E			55%
24		E			65%
25		E			65%
26		E			65%
27		E			65%
28		F			75%
29		F			77%
30		F			79%
31		F			81%
32		F			83%
33		F			85%
34		F			87%
35		F			89%
36		F			91%
37		F			93%
38		G			99%
39		G			99%
40		H			72%
41		H			72%
42		I			82%
43		I			85%
44		I			88%
45		I			91%
46		I			94%
47		I			97%
48		A-2			72%
49		A-2			74%
50		B-2			76%
51		B-2			78%
52		I			100%
53		A-2			80%
54		A-2			82%
55		B-2			84%
56		B-2			86%
Data

 

[Dave Patton]

Averageifs.xlsm
	A	B	C	D	E	F
1		Name	Average Grade		Top 5 Name	Average Grade
2		D	99.0%		D	99.0%
3		G	99.0%		G	99.0%
4		B	92.0%		B	92.0%
5		C	92.0%		C	92.0%
6		I	91.0%		I	91.0%
7		A	89.5%
8		F	84.0%
9		A-2	83.2%
10		B-2	81.0%
11		Z	79.5%
12		H	72.0%
13		E	61.7%
Report

Cell Formulas
Range	Formula
B2:C13	B2	=LET(u,UNIQUE(FILTER(rN,rN>"")),SORT(HSTACK(u,AVERAGEIFS(rG,rN,u)),2,-1))
E2:F6	E2	=LET(u,UNIQUE(FILTER(rN,rN<>"")),TAKE(SORT(HSTACK(u,AVERAGEIFS(rG,rN,u)),2,-1),5))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
rG	=Data!$E$2:$E$56	B2, E2
rN	=Data!$B$2:$B$56	B2, E2