Support with Formula or Macro for matching cells and finding max range

[MrsTeach]

Hi, I'm a teacher and I'm trying to put together some data for progression.

I'd be really grateful if anyone could point me in the right direction.

Book1
	A	B	C	D	E	F
1	Cohort ID	Size	Progression
2	Coniston	22	3
3	Derwent	14	11
4	Coniston	22	5
5	Coniston	22	21
6	Derwent	14	14
7
8
9	Size of Cohort with biggest difference in progression from lowest to highest
10
11
12
Sheet1

I'm really rusty with Excel!

The above is an example of a huge dataset of pupils in our group of schools. I'm looking to return the 'Size' of the cohort with the biggest difference in Progression. If there's more than one cohort with the same difference, then just the first value.

Any help would be fantastic! I can't get my head around it!

 

[JamesCanale]

=LET(z,A2:A6,y,B2:B6,x,C2:C6,a,UNIQUE(z),b,MAXIFS(x,z,a),c,MINIFS(x,z,a),d,b-c,e,MAX(d),f,INDEX(a,XMATCH(e,d,0)),XLOOKUP(f,z,y))

 

[Skybluekid]

How about this:

Book1
	A	B	C	D
1	Cohort ID	Size	Progression	Difference
2	Coniston	22	3	0
3	Derwent	14	11	0
4	Coniston	22	5	5
5	Coniston	22	21	26
6	Derwent	14	14	14
7
8	Coniston	22
Sheet2

Cell Formulas
Range	Formula
D2:D6	D2	=SUMIF($A$2:A2,A2,$C$2:C2)-MINIFS($C$2:C2,$A$2:A2,A2)
A8:B8	A8	=FILTER(A2:B6,D2:D6=MAX(D2:D6))
Dynamic array formulas.

 

[MrsTeach]

Wow! That's complex. I'll work my way through trying to understand it. Thank you so much!
Probably a dumb question, but how do I get the returned value (22) to show in the cell the formula's in? Currently just shows this:

But returns the correct value in the formula builder:

 

[JamesCanale]

I can't say why excel for the web wouldn't show the number. I'm not too savvy with that. I use it occasionally, and it seems to work for me. I've never seen it stuck as the formulatext.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F
1	Cohort ID	Size	Progression
2	Coniston	22	3		Coniston	22
3	Derwent	14	11
4	Coniston	22	5
5	Coniston	22	21
6	Derwent	14	14
7
8
Main

Cell Formulas
Range	Formula
E2:F2	E2	=LET(u,UNIQUE(FILTER(A2:B100,A2:A100<>"")),m,MAXIFS(C:C,A:A,INDEX(u,,1)),TAKE(SORTBY(u,m,-1),1))
Dynamic array formulas.

 

[MrsTeach]

=LET(z,A2:A6,y,B2:B6,x,C2:C6,a,UNIQUE(z),b,MAXIFS(x,z,a),c,MINIFS(x,z,a),d,b-c,e,MAX(d),f,INDEX(a,XMATCH(e,d,0)),XLOOKUP(f,z,y))

Sorry to ask again, but could you also possible help with this?

Book1
	A	B	C	D
1	Set ID	Pupil ID	Sex
2	1	1111	1
3	1	2222	1
4	1	3333	2
5	1	4444	1
6	2	5555	2
7	2	6666	1
8	2	7777	2
9	2	8888	2
10	3	9999	1
11	3	1000	2
12	3	1100	1
13
14	Total Number of All Female Sets (Where female = 2)
15
16
Data

I'm trying to calculate the total number of sets which are all female (female = 2)

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks