Run at parkrun events different numbers of consecutive times

[ollyhughes1982]

Hi,

I have a list of different parkrun events that I have completed and the number of times that I have completed each one (image attached).

There is a challenge in the parkrun community where you calculate your “Rutter Triangle” (not sure where the name comes from!). i.e. Run at parkrun events different numbers of times and then find the longest consecutive run from 1 upwards. For example, in my list I have lots of 1s, so that’s 1; then two 2s at Pontypridd and Severn Bridge, so that’s 2; then three 3s at Aberbeeg, Penallta and Coed Cefn-pwll-du, so that’s 3 - Therefore my Rutter Triangle number is 3. (1, 2 and 3)

As an example, to increase my number to 4 I would need to run either Aberbeeg, Penallta or Coed Cefn-pwll-du once more, to move that event’s total completed up to 4. So I would then have 1, 2, 3 and 4 (Rutter Triangle number of 4).

Additionally, to increase my number to 6 I would need to run either Aberbeeg, Penallta or Coed Cefn-pwll-du twice more and either Aberbeeg, Penallta or Coed Cefn-pwll-du once more to move those event’s totals completed up to 4 and 5. So I would then have 1, 2, 3, 4, 5 and 6 (Rutter Triangle number of 6). N.B. I already have the 6, so this would bridge the gap of 4 and 5.

Hope this makes sense!

Thanks in advance!

Olly.

 

[RobP]

Hi Olly, now we've understood that you know what you need to do in terms of your runs .. what's actually your question to us ?

and does your consecutive number always have to start from 1 ? (or if you ran 3 of them, 17,18 and 19 times - does that mean you have a Rutter of 19 ?)

cheers
Rob

 

[ollyhughes1982]

Hi Olly, now we've understood that you know what you need to do in terms of your runs .. what's actually your question to us ?

and does your consecutive number always have to start from 1 ? (or if you ran 3 of them, 17,18 and 19 times - does that mean you have a Rutter of 19 ?)

cheers
Rob

Hi.

Thanks for your reply. Yes, the run has to start at 1, it's the unbroken sequence from 1 upwards, ignoring the duplicates. Initially, I just want to have a formula that looks at column B and calculates the Rutter number, which I will have located in a separate worksheet where I have my summary stats.

Thanks

 

[ollyhughes1982]

It would also be great to be able to have it visualised in its own worksheet, if possible. I have attached an image of how this would be expected to look (below). I have manually populated the entries in yellow and included the logic on the right.

I have also attached a link to a copy of the file, to make things easier. OneDrive Link: Rutter Triangle.xlsx

Thanks again!

 

[RobP]

Hi Ollie,

assuming your data (ie. number of times each event was run) is in Range B2:b10, you can use this formula to get your Result.
If you are adding events continuously, you might be better to replace B2:b10 with B:B - as that will then always look at all col. B. ie. every time a new event gets added.

=LET(arr,SORT(UNIQUE(FILTER(B2:B10,1))),seqn,SEQUENCE(COUNT(arr),1,1),x,COUNT(IF(arr-seqn=0,1,"")),x)

Not sure quite to understand what you are trying to show above, so might need more explaining - or maybe this formula will get you where you want to be for you to finish your sheet.

Let me know how to get on.. (I notice you stay East, rather then venture West also .. so unlikely to see you in Neath any time soon !

Rob

 

[ollyhughes1982]

Hi Ollie,

assuming your data (ie. number of times each event was run) is in Range B2:b10, you can use this formula to get your Result.
If you are adding events continuously, you might be better to replace B2:b10 with B:B - as that will then always look at all col. B. ie. every time a new event gets added.

=LET(arr,SORT(UNIQUE(FILTER(B2:B10,1))),seqn,SEQUENCE(COUNT(arr),1,1),x,COUNT(IF(arr-seqn=0,1,"")),x)

Not sure quite to understand what you are trying to show above, so might need more explaining - or maybe this formula will get you where you want to be for you to finish your sheet.

Let me know how to get on.. (I notice you stay East, rather then venture West also .. so unlikely to see you in Neath any time soon !

Rob

Thanks, will give this a try.

Ah, you're a parkrunner?! I'm trying top do a challenge where i do all new ones in 2022, and I've done all the South Wales one, so won't be until at least next year!

 

[RobP]

no, not really - I just walk the dog and know I need to get out of the Gnoll before the chaos starts every Saturday ...

Hope the formula works for you.

 

[ollyhughes1982]

Hi Ollie,

assuming your data (ie. number of times each event was run) is in Range B2:b10, you can use this formula to get your Result.
If you are adding events continuously, you might be better to replace B2:b10 with B:B - as that will then always look at all col. B. ie. every time a new event gets added.

=LET(arr,SORT(UNIQUE(FILTER(B2:B10,1))),seqn,SEQUENCE(COUNT(arr),1,1),x,COUNT(IF(arr-seqn=0,1,"")),x)

Not sure quite to understand what you are trying to show above, so might need more explaining - or maybe this formula will get you where you want to be for you to finish your sheet.

Let me know how to get on.. (I notice you stay East, rather then venture West also .. so unlikely to see you in Neath any time soon !

Rob

That worked and calculates correctly. Thanks.

To do the second part, if you have a look at the workbook, it contains the source data for the table, which includes the dates and other information for each run. I think some sort of FILTER formula would be needed.

 

[ollyhughes1982]

I think the logic would be:
Look at the '# of Runs' column in the 'All Completed Runs - Rutter T' worksheet and then lookup in 'All Completed Runs' for which runs have been completed that number of times, i.e. appears in that number of rows in column C. If the answer is multiple items, then take the latest date entry for the events with that number of runs. If the answer is a single event then also just take the latest data that event was run.

 

[RobP]

Looking at your workbook, the problem you have is that there is absolutely no link between All Completed Runs, and your All Completed Rutter Runs. If I imagine your Rutter sheet is blank, and you want to populate the event name and date, just by looking at a number 1, or Number2 in col. A, theres nothing that exists with a "2 or above" on your all runs sheet. So its not quite as straightforward as you'd like.

There might be something possible, but it requires a whole new train of thought.. and time. So best not to hold your breath for too long .. Maybe someone else can also have a think about it here for you..
Rob