One of the highlights of my life was travelling to the 2016 summer Olympics in Rio de Janeiro to watch my sister, Krista DuChene, run in the women’s marathon. Her story is inspirational. Krista fractured her leg on April 28, 2014 during a half marathon and then in August of 2015 she broke a bone in her foot only one year before the Olympics.
Injuries
Most of us would simply stop running. Not Krista. She doesn’t give up easily. Focus, determination and grit are words that I would use to describe her.
I can remember the first time that Krista broke her leg. She was only 1 and had only just started walking on her own. I can vividly remember her with her cast. When she cried we would give her toys and play with her. When the cast came off mom told us not to hand her the toys but to set the toys a few feet away so that Krista would have to get moving again. I can remember an adult saying “She might never walk properly“. Ahhh, wrong!
Qualifying for the Olympics!
Despite her injuries, Krista also had to pass the Canadian Olympic committee’s challenging criteria to quality for the Olympics. She trained diligently, met the requirements (Globe & Mail article, Toronto Star Article), stayed healthy and made it to the Olympics in August of 2016!
2016 Summer Olympics in Rio
A group of us traveled down to Rio to cheer for Krista. During the race, we saw video updates on the big screen and diligently watched the ticker at the stadium. We were all so proud and relieved when we saw her enter the stadium and cross the finish line! What an incredible experience.
This was our group relaxing at Canada House after the race.
Women’s 2016 Olympic Marathon Data
A few weeks ago I was trying to find the race data. Did she finish 34th or 35th? What was her time? 2:35:00 or 2:36:00? I finally found some data from www.olympic.org/
Re-arranging the Data
Each entry was three rows, there were logos and hyperlinks. We can’t easily analyze this data unless we re-arrange it. I thought of several ways to re-arrange the data but using some INDEX functions was the easiest (see my Excel file below). Now the data looks like this! Each row is a runner, each column is a field.
Pivot Table and Formulas Answer Questions
I created some Pivot Tables to answer questions like these:
- Fastest average time by country (excluding runners who didn’t finish the race)
- Percent of athletes who did not finish the marathon and those that did finish the marathon
- Average marathon time (for those that finished)
Sheet “Quartiles” calculates the quartiles (splitting runners who finished the race into four groups).
Olympic Formula Challenge
There were triplets who qualified for the women’s marathon. Can you create a solution (1 formula or several steps) that will list all three names?
Use the data in columns G and H of sheet “Cleanup via INDEX formulas”.
HINT: They have the same last name, their first names all start with the same letter and as you might expect they are all from the same country.
Download my Excel File
Here is my Excel file. or get it here from my OneDrive.
HAPPY CANADA DAY! 150 YEARS TODAY!
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.
Away from Excel I enjoy learning Spanish, playing Chess, hanging out with Cali and Fenton and reading Excel books 🙂