Ranking lap times with 2 decimals

[Lawrence Parker]

So im trying to rank my lap times but i keep getting the error that my lap time is a text value and not a number value because it has two decimal places.

e.g
2.23.340 i get an error but 2.23 works.
is there a way to rank numbers with two decimal places.

Formula =RANK(D3:D27,LapTimes,1)

picture of my sheet
https://gyazo.com/4ef6b4384a66136d2b8846ccab78af57

 

[oldbrewer]

is the time formatted as hh:mm:sss

 

[higrm]

More likely mm.ss.decimal seconds. So the example is probably 2 minutes 23.340 seconds.

 

[Lawrence Parker]

yes

 

[higrm]

I think you may need a helper column to convert to time values before you can rank. Use this formula on each entry: =TIMEVALUE(SUBSTITUTE(D3,".",":",1))
It is up to you to format it as a time or leave it as a decimal. But you can now rank this helper column.

 

[Fluff]

How about

Excel 2013/2016
	D	E
2	Laptime	Rank
3	2.23.340	2
4	3.10.698	3
5	2.11.969	1
Engine

Cell Formulas
Range	Formula
E3	=SUMPRODUCT(--(TIMEVALUE(SUBSTITUTE(D3,".",":"))>TIMEVALUE(SUBSTITUTE(D$3:D$5,".",":"))))+1