sorting race results into overall positions... any advice?

I've spent hours googling this, with no luck. I am sure it must be a fairly common scenario, too...

I need an efficient way to sort out race results. Racers, wearing bib numbers 01 - 99, compete in a number of races. We record them coming over the finish line. And we do a number of races. So we end up with a table that looks like

POS Race 1 Race 2 Race 3 Race 4
1st 16 24 24 05
2nd 24 05 27 14
3rd 05 16 33 27
4th 14 27 16 1
5th 27 33 05 42
6th 33 14 22 24
7th 1 8 19 16
etc

etc etc. (And just to reiterate, the numbers in the table are the bib numbers. The finishers come over the line far too fast to be able to do anything other than just write down the numbers.)

So what we want to be able to do ideally is to extract the data from that table with some elegant formula rather than by pencil and paper, or some brute force humungous nested IF statement, to end up with the data thus:

Racer Race 1 Race 2 Race 3 Race 4 TOTAL OVERALL
01 7 9 9 4 29 5
02 10 8 etc etc etc etc
03 14 10 etc etc etc etc
04 27 11 etc etc etc etc
05 3 2 5 1 11 2
06 13 18 etc etc etc etc
07 15 22 etc etc etc etc

ie lifting out the finishing position for each racer, from each race, totalling them, and then finally (pushing my luck here perhaps!), creating an overall position for each racer based on their combined total performance. (I appreciate that I could just do a sort at this stage, once I have the totals. But obviously it would be nice to have the spreadsheet do ALL the work, if possible!)

As said, I'm sure this requirement must occur on a regular basis in sporting events so I'm sure the solution is out there, apologies if it's already well discussed/documented elsewhere!

many thanks

BillyD

Hi Leelnich, thank you SO much!! That's amazing. Exactly what we needed, and indeed more, with that extra neat addition of actually interrogating the table to see which numbers are there, rather than just listing every possible number. I had no idea that excel could be pushed so hard.

So, if I may, could I just pick your brains on the one final stage which really would be the icing on the cake for making our scoring system as efficient as possible (and as you can imagine, when you're dealing with dozens of mud soaked and hyper kids, in the rain and wind, stuff going on everywhere, every little thing that makes life easier for the poor sod who's got the job of getting the results sorted is just a huge bonus...!)

It's just a case of sorting the final results table into overall finishing order. I have attached a proper table of real results, with your sorting table included, so it's showing the combined total scores of each athlete. all we need now is to show their overall finishing position. I know we could simply re-sort the table by overall scores and write the results out from that, but it would be brilliant if that final stage could be automated too...

And AliGW, many thanks for all the advice and welcome, which I will endeavour to follow from here on.

Do you mean like this?

Um, no, sorry maybe I explained it wrong. In that v2 file I had uploaded, there is the final blank column titled POSITION. That is the column I want to 'solve' with a formula - the overall finishing position for that athlete based on the points total shown in the previous column. The extra column I had added entitled 'correct positions' was purely there to show the results that we are trying to get to, worked out by hand rather than excel magic.

OK, all sorted, just discovered the RANK function. Duh. Too easy.

But I don't think I would easily have discovered the MATCH/INDEX math for the other tasks, so thanks again everyone. Super helpful and much appreciated!!!

@billyd333 I have been keenly interested in this thread.
Please post your final version including the "rank" function.

Very good!

Here you go Vibert_c - hope it's as of much use to you as it has been to me!