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).
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
- 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.
Sign-up for our FREE Excel tips newsletter:
Here is a smart way to become awesome in Excel. Just signup for my FREE Excel tips newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
Leave a Reply
|14 Basic Skills for Chart Makers (Big question: How many do you have?)||2 Great Pieces of Advice for Chart Makers, Dashboard Designers and Story Tellers Everywhere|