RANK on race times

J

JockW

Hi,
I have a number of times (mm:ss) which I'd like to rank by quickest first.
Having problems in that even though formatted as mm:ss, ISNUMBER (A1) gives
FALSE thus, RANK(A1,A1:A20,1) returns #N/A.
I'd also like it to ignore blank cells (currently A15:A20) as these would be
future races.
Ok, so what's the best plan here: format as text or mm:ss?
 
M

MartinW

Hi Jock,

The error is because your value is text and not an actual time.
Copy an empty cell
Select your data (A1:A20 in your example)
Go to Edit>Paste Special and check add and OK

That will convert your data to time and will probably
change the cell format to general (whatever the format
of the cell that you copied was). Reset the format to
custom [hh]:mm

Then put this formula in B1 and drag down to B20
=IF(A1="","",RANK(A1,$A$1:$A$20,1))

HTH
Martin
 
J

JockW

Thanks Martin. Sorted.
I'm confsed as to why the formatting is [hh]:mm rather that mm:ss when all
times are in minutes and seconds.???
--



MartinW said:
Hi Jock,

The error is because your value is text and not an actual time.
Copy an empty cell
Select your data (A1:A20 in your example)
Go to Edit>Paste Special and check add and OK

That will convert your data to time and will probably
change the cell format to general (whatever the format
of the cell that you copied was). Reset the format to
custom [hh]:mm

Then put this formula in B1 and drag down to B20
=IF(A1="","",RANK(A1,$A$1:$A$20,1))

HTH
Martin



JockW said:
Hi,
I have a number of times (mm:ss) which I'd like to rank by quickest first.
Having problems in that even though formatted as mm:ss, ISNUMBER (A1)
gives
FALSE thus, RANK(A1,A1:A20,1) returns #N/A.
I'd also like it to ignore blank cells (currently A15:A20) as these would
be
future races.
Ok, so what's the best plan here: format as text or mm:ss?
Click to expand...
Click to expand...
 
Top