ollyhughes1982
Well-known Member
- Joined
- Nov 27, 2018
- Messages
- 677
- Office Version
- 365
- Platform
- MacOS
Hi I am trying to find a solution to problem I want to solve on my parkrun results spreadsheet. The definition of the calculation I need to make is: 'The total streak of finish positions, but starting anywhere, in any order. e.g. If you have finished: 123, 120, 304, 122, 201, 121 It would be 4, since you have: 120,121,122 and 123.
I have just manually checked my 'floating finish position' from the 'Finishing Position #' (F) column in the 'All Completed Runs' worksheet of my parkrun spreadsheet, and the answer is 24 - running from 70th-93rd. If anyone might be able to find a way to achieve this in an Excel formula, I would be really grateful. I have tried and failed! I want to populate the yellow cell (B43) in the 'Summary' worksheet with the correct result. I would also like, if possible to put these sequences in a list in the 'All Completed Runs - FFP-I' worksheet (also highlighted yellow).
My file is here:
https://1drv.ms/x/s!AtIvkRgReIvwgTdNIGandjTeGGdr?e=vXp7cq
Thanks!
Also posted Find consecutive strings of numbers from out of order list, and calculate longest string
and here on Reddit
I have just manually checked my 'floating finish position' from the 'Finishing Position #' (F) column in the 'All Completed Runs' worksheet of my parkrun spreadsheet, and the answer is 24 - running from 70th-93rd. If anyone might be able to find a way to achieve this in an Excel formula, I would be really grateful. I have tried and failed! I want to populate the yellow cell (B43) in the 'Summary' worksheet with the correct result. I would also like, if possible to put these sequences in a list in the 'All Completed Runs - FFP-I' worksheet (also highlighted yellow).
My file is here:
https://1drv.ms/x/s!AtIvkRgReIvwgTdNIGandjTeGGdr?e=vXp7cq
Thanks!
Also posted Find consecutive strings of numbers from out of order list, and calculate longest string
and here on Reddit
Last edited by a moderator: