Formula to work out 8th,7th,6th...1st Fastest times and put into lanes for a race

[Londonproblems85]

Hello,

I am organising an Athletics event coming up next month and I am trying to work out a formula which will take the athletes details and times from a list which contains all the heat times and give me the top 8 but will also put them into their correct lanes e.g.

Lane 1 - 8th Fastest
Lane 2 - 6th Fastest
Lane 3 - 4th Fastest
Lane 4 - 2nd Fastest
Lane 5 - Fastest
Lane 6 - 3rdFastest
Lane 7 - 5th Fastest
Lane 8 - 7th Fastest

The picture attached is what I am working on. Please ignore the relay table as that is not affecting this information.

Thanks for all help

 

[NARAYANK991]

Hi ,

Uploading a workbook rather than a screenshot will make it easier for others to help.

See the attached file and see if the calculations there help.

Narayan

 

[Londonproblems85]

Hi Narayan,

Good point. Attached is what I am working on.

What I need is a formula which will autpopulate column K with the Athlete numbers from Column D based on their rank which will fill the table '100m Final'

Lane 1 - 8th Fastest, in the example Athlete 667
Lane 2 - 6th Fastest
Lane 3 - 4th Fastest
Lane 4 - 2nd Fastest
Lane 5 - Fastest
Lane 6 - 3rdFastest
Lane 7 - 5th Fastest
Lane 8 - 7th Fastest, in the example Athlete 876

Thanks for the assistance

Matt

 

[NARAYANK991]

Hi Matt ,

See your file now.

NumberOfLanes is a named range , which I have set to 8.

Narayan