Various parkrun calculations

[ollyhughes1982]

Hi, I'm having trouble with some worksheets in my parkrun results workbook. Every tab and every cell that are highlighted in yellow are the ones I need to find formulas for. I have manually populated them for now, so you can see what I'm trying to achieve.

I suspect most of them will likely be unique filter type formulas, similar to some I already have in other worksheets. - I have attempted to work these out, but am struggling.

If anyone could help me with the formulas for these, it would be greatly appreciated.

https://1drv.ms/x/s!AtIvkRgReIvwgTdN...eGGdr?e=mah25R

Thanks in advance!

Also post on Mr. Excel forum:

https://www.mrexcel.com/board/thread...tions.1130009/

[AliGW]

Please use our attachment facility for your sample file. Thanks.

[ollyhughes1982]

Hi. They are multiple worksheets in a workbook. I will try and chop them down so I can post examples, but they may still be too large as sheets are interconnected.

[ollyhughes1982]

Hi. First 5 cut-down files attached. Thanks again!

[ollyhughes1982]

Hi. Cut-down files 6-10 attached. Thanks again!

[ollyhughes1982]

Hi. Cut-down files 11-15 attached. Thanks again!

[ollyhughes1982]

Hi. Cut-down files 16-20 attached. Thanks again!

[ollyhughes1982]

Hi. Cut-down files 21-22 attached. Thanks again!

[JeteMc]

This proposal is in reference to the first file attached to post #8.
This proposal adds a helper column (XFD) to the All Completed Runs sheet which is populated using: =MOD(A3,100)=0
A small table is added to the All Completed Runs - TOR sheet in cells E3:F5
The formula for column A is: =IFERROR(INDEX('All Completed Runs'!C$3:C$232,AGGREGATE(15,6,(ROW(A$3:A$232)-ROW(A$2))/('All Completed Runs'!XFD$3:XFD$232=TRUE),ROWS(A$1:A1))),"")
The formula for column B is: =IF(A3="","",INDEX(F$3:F$5,MATCH(INDEX('All Completed Runs'!A$3:A$232,AGGREGATE(15,6,(ROW(A$3:A$232)-ROW(A$2))/('All Completed Runs'!XFD$3:XFD$232=TRUE),ROWS(A$1:A1))),E$3:E$5,0)))
The formula for column C is: =IF(A3="","",INDEX('All Completed Runs'!E$3:E$232,AGGREGATE(15,6,(ROW(A$3:A$232)-ROW(A$2))/('All Completed Runs'!XFD$3:XFD$232=TRUE),ROWS(A$1:A1))))
Let us know if you have any questions.

[Fluff13]

The joy of the new functions.
In A3
=UNIQUE(FILTER('All Completed Runs'!C3:C2002,COUNTIF('All Completed Runs'!C3:C2002,'All Completed Runs'!C3:C2002)>99))
Then in B3 (if you're happy with a slight change in format)
=FLOOR.MATH(COUNTIF('All Completed Runs'!C3:C2002,A3#),100)&"+"
C3
=IF(A3="","",INDEX('All Completed Runs'!$E$3:$E$2002,AGGREGATE(15,6,(ROW('All Completed Runs'!$E$3:$E$2002)-ROW('All Completed Runs'!E3)+1)/('All Completed Runs'!$C$3:$C$2002=A3),100)))

[ollyhughes1982]

Wow.Thanks all