Support with formula or macro analysing pupil data

[iCompute]

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.

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!

[#2]

@iCompute Questions:

1. Which version of Excel are you using? The attached spreadsheet uses functions that require Excel 2019 or later. If you have Excel 2021 or later, the formulas can be written more clearly using the LET function, as I did in the Analysis2021 worksheet.
2. Is there a date and/or time associated with each row? Are the rows in chronological order, or at least that each cohort's rows are in chronological order? My solutions assume the latter is true.
3. For any one cohort… Do the Progression values always increase over time (i.e., between rows)? If not, is it possible for them to decrease from one time (or row) to the next? If the Progression values are the same or higher for each successive row, the change in Progression can be calculated using MAXIFS and MINIFS functions, as my solutions do.
4. For any one cohort, can the Size value change between rows? If so, which value (first or last or largest or smallest or average or …?) should be reported? The attached solutions use the average.