Excel Challenge 41: Transforming Data With Excel Functions

Are you ready to test your problem-solving and Excel formula skills? This challenge tests your ability to convert data from one format to another using Excel functions.

Everything you need to participate in the challenge can be found on this page. To take part:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) on which the challenge is based.
• Download the Excel worksheet you will use to complete the challenge tasks.
• Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.

---

The challenge

Here’s the scenario to be solved from the download file:

We are organizing the start waves for the London Marathon based on each athlete’s estimated completion time. 

Points to note:

• There are three waves: blue, red, and green.
• The fastest athletes should be assigned to the blue wave, and the slowest athletes should be assigned to the green wave. 
• We know each athlete’s 10-kilometer time. 
• The total distance for the marathon is 42 kilometers.

✅ STEP 1: Calculate each athlete’s estimated marathon finishing time.

Calculate the finishing time for each athlete based on their 10K time. 

Things to note:

• Fill down for all athletes. 
• Ensure the formatting is h:mm.

✅ STEP 2: Generate a random bib number. 

Next, we need to generate a random bib number for each athlete. 

Things to note:

• Bib numbers should be between 10000 and 99999. 
• There should be no duplicate bib numbers. 
• Remove any volatile functions. 

✅ STEP 3: Convert the data to an Excel table. 

Convert the dataset to an Excel table and name the table ‘Marathon2025’. 

✅ STEP 4: Choose columns and sort the data.  

Next, in columns G to I, we need to pick out of the dataset the following columns, ‘Athlete’, ‘Bib Number’ and ‘Marathon Time’. 

Things to note:

• Use column index numbers in the formula. 
• Ensure the marathon times are formatted correctly as h:mm (hours and minutes). 

Sort the new list by marathon time and then by athlete name in ascending order so the fastest athlete is at the top of the list.

✅ STEP 5: Calculate the total number of athletes. 

In cell C38, calculate the total number of athletes. 

✅ STEP 6: Organize the athletes into start waves.  

Finally, we need to organize the athletes into start waves based on their time. The fastest athletes should be in the blue wave (starting first) and the slowest athletes in the green wave (starting last). Athletes should be organized evenly.  

How quickly can you complete this challenge? Fifteen minutes? Ten? Less?

If you’re just getting started with Excel, all the knowledge you need to solve this challenge has been shown in the following resources: 

---

 Read:

	Excel SORT and SORTBY functions
	Working With Excel Dynamic Arrays

 

Watch:

If you’re already proficient with Excel, this should be quite easy.

We hope you'll enjoy taking part in this challenge! Have fun!

---

The solution

Did you remember to share your method in our Slack channel? If you're stumped, check out how other learners solved this problem. And don't forget to watch Deb Ashby's solution below.

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical real-world Excel skills.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial