Formula 1 Style Sorting of Times (Durations) in Excel

The other day I was watching Formula 1 on TV. I think it is the ideal game to follow for a lazy dude like me. It is on every other weekend. It takes .32 seconds to understand the game and 3.2 seconds to know the points and scoring mechanism. But I am not here to convince you to follow the game. I am here, however to convince you to follow my blog (and twitter too).

Tee hee…

On a more serious note, while looking at score boards, it struck me,
“how about writing excel formulas for sorting a list of durations (or numbers) in the formula 1 order?”

Not clear? See this:

Here is the formula:

• Assuming the durations are in the range B3:B9
• First cell has the formula =SMALL($B$3:$B$9,ROWS(B$3:$B3))
• Subsequent cells have the formula ="+ " &TEXT((SMALL($B$3:$B$9,ROWS(B$3:$B4))-$C$3)*24*60*60,"0.0")&" secs"

Note that I have assumed the range B3:B9 is unsorted.

How the formula works?

• Just like a formula 1 car – smooth and fast 😛
• We are using small() to fetch the smallest value from the range of durations for the first formula.
• For the subsequent formulas, we just subtract the smallest value form nth smallest value and use TEXT() to show it in + xx.xx secs format.
• We are using ROWS() for … ?

If your brain needs a kick start to master excel formulas…

Go ahead and purchase my Excel Formula 1 – the fastest (and the funnest) way to learn excel formulas e-book. You can learn 75 most frequenlty used excel formulas in no time.