Statistical
Rank race results and sum stopwatch lap splits (hundredths of a second)
Total lap splits to 0.01-second precision, rank finish times ascending with RANK(..., 1) so fastest wins 1st place, and compute gap behind leader.
Set RANK order = 1 so the lowest stopwatch time ranks 1st
By default, RANK(value, range) sorts DESCENDING (order = 0), which awards 1st place to the highest sales figure—and last place to the fastest sprinter or swimmer!
Passing 1 as the third argument RANK(FinishSec, $D$2:$D$5, 1) ranks smallest-to-largest so 82.50s beats 83.35s. Storing splits in decimal seconds (or parsing "1:23.35" via VALUE(TEXTBEFORE(s,":"))*60 + VALUE(TEXTAFTER(s,":"))) avoids spreadsheet hh:mm rounding of hundredths.
| Athlete (A) | Lap 1 s (B) | Lap 2 s (C) | Total s (D) | Place | Gap Behind 1st |
|---|---|---|---|---|---|
| Aria | 41.25 | 42.1 | 83.35 | 2 | +0.85 s |
| Bo | 40.8 | 41.7 | 82.50 | 1 | 0.00 s (Leader) |
| Cruz | 42 | 43 | 85.00 | 4 | +2.50 s |
| Dani | 41.5 | 41.9 | 83.40 | 3 | +0.90 s |
Ascending race rank, time gap, and mm:ss.hh parser
The example rounds total decimal seconds to hundredths before ranking. Use the precision and tie policy required for your event.
=RANK(D2, $D$2:$D$5, 1)Seconds behind race leader
Subtracts the fastest time MIN($D$2:$D$5) from each athlete's finish.
=ROUND(D2 - MIN($D$2:$D$5), 2)Inputs and interpretation
RANK with order 1 uses competition ranking: tied first places are 1,1,3. Decide the rounding and tie policy before ranking; rounded centiseconds are not a universal sporting rule. Exclude non-finishers rather than representing them as zero. The shown parser expects minutes:seconds.hundredths, with seconds below 60; it is not a general hours or locale-aware parser.
Try the example
Use the original values or change them to test your understanding.