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.
