Challenge #85: How Did Tara Place in Competition?

[BenMoss]

Ha, thanks @JoeM!

Never stop learning!

[JoeM]

The solution is posted!

[Natasha]

Solution attached. Btw @JoeM it looks like you attached the start file twice instead of the solution.

Spoiler

[JoeM]

Thanks @Natasha! Updated!

[NicoleJohnson]

My solution!

Spoiler

My results gave Tara 11th place overall, and 54th in the Interval event. First, I transposed the data and then isolated Tara & Carisa to calculate the time that Tara should have had for the Interval event. I joined the data back together, and then sorted by event & used a multi-row tool to determine what the ranks should be with the addition of the Tara's new time for Interval.I then joined that information to a list of Bib#'s summarized by only those with 4 timed events. Finally, I calculated the average rank per Bib# to determine Tara's final ranking.

[samjohnson]

I don't trust authority so I eliminated the initial rankings of each event and re-calculated them ;)

My solution is longer because I wanted to take a more step-by-step approach, and I didn't try to automatically calculate Tara's Interval time; I set it manually. (This hurt me to do this, but there's no harm as we're not curing cancer, right?)

Finally, after re-calculating all of the event rankings I took the overall average of those ranks as a Double and sorted them and added another record ID, which became the final place for each competitor. I finished by highlighting Tara's entire record.

Fun challenge.

[LordNeilLord]

I initially got a rank of 13, so I checked and double checked the workflow but couldn't find the issue! After several moments of head scratching...I finally realised that the times were in mm:ss.ms and not hh:mm.ss....a few quick formula changes and I got the correct result.

Spoiler

[jdunkerley79]

That was fun... Not sure I've got the most efficient solution

Spoiler

[SeanAdams]

I feel your pain Tara - timekeeping fail is very emotionally traumatic!

@Joe_Mako - very impressive solution!

Spoiler

[robcarroll]

Done!