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.
13 Responses to “Formula 1 Style Sorting of Times (Durations) in Excel”
Love the example and can find a real world use of me right now. Very "timely". 😉
Rows tell you what level of the smallest value you are pulling. For example b3:b4, says get the second smallest value, b3:b6 the fourth and so on.
The part I cannot replicate is that my formulas result in the seconds being rounded not the 1/10th of a second you show. Hmm?
Hi folks,
Why use text function in that formula ???
the result is no more directly usable for other calculations...
I prefer simply :
=SMALL($B$3:$B$9,ROWS(B$3:$B4))-$C$3
but personnal cell format :
+ [s].0" secs"
I had a clever observation to make, but Modeste has beaten me to it!
If for some reason you still had to use TEXT, replace this:
*24*60*60,"0.0")
with this:
,"[s].0)
@PragmaticCynic: I have used data that has fractional seconds. Just that it is not shown on the screen. You can get it from last weeks F1 results page here: http://www.formula1.com/results/season/2009/814/
@Modeste: Agree with you. I have thought of cell format codes but removed that since it will introduce one more step in the process. But you have a very good point. And I didnt know about the formatting code [s].0, thank you for teaching me that.
@Jon: Thank you 🙂
a question for the Jedi Masters:
I want to compare prices instead of times, and I'd like to use the same logic, but showing the differences in rounded hundreds (and perhaps rounded units, as thousands, millions, etc?).
hwo can I achieve that?
Kind Regards,
Martín
@Martin: The logic should be similar and the formulas will be shorter (times are messy to handle, numbers are easy). For eg. this should work...
="+ " &ROUND((SMALL($B$3:$B$9,ROWS(B$3:$B4))-$C$3),-2)+"$s"
Hola, estoy tratando de realizar el ejemplo, pero no encuentro la similitud de la fórmula small, para la versión en español. Puedes ayudarme.
Hello, I am trying to make the example, but I can not find the similarity of the small formula to the version in Spanish. Can you help me?
Hola Jair ,
for translations of key words you should go there:
http://www.rondebruin.com/atptranslator.htm
but precisely for the translation of "SMALL", you have to activate the VBA complementary macros .
"************************************************
Hola Jair
para la traducción de las palabras clave que usted debe ir:
http://www.rondebruin.com/atptranslator.htm
pero precisamente para la traducción de "SMALL" (¿ pequeño), usted debe activar la macro de VBA complementarias
;o)) (google translator)
Hi, does the function SMALL a complement make for you, or for Microsoft? Do you have some .xls example that I can run on my computer. I used http://www.rondebruin.com/atptranslator.htm, but the function SMALL is not there.
Thanks you.
@Jair: Did you try looking here? http://cherbe.free.fr/traduc_fonctions_xl97.html
It says SMALL is K.ESIMO.MENOR in Spanish. I am not able to test it, but I you can give it a try.
It's ok. Thanks you. k.esimo.menor and k.esimo.mayor are function returning n small and n major number from a list.
Jair,
for future reference:
Microsoft delivers an excel file with every version of Office / Excel that includes a comprehensive translation table for worksheet functions and vba functions from English to the installed language. Usually the workbook is called VBALIST.XLS and it is stored somewhere in the folder Program Files / Microsoft Office. Maybe you want to do a search for VBA*.XLS and check what you are finding?