# Excel Challenge 41: Transforming Data With Excel Functions

Deb Ashby

#### Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

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.
• Put yourself to the test!

Take the challenge

Think you have what it takes to solve this challenge? Download the workbook and get started!

## The challenge

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:

### 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!

Take the challenge

Think you have what it takes to solve this challenge? Download the workbook and get started!

## 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.