RANK on race times

[Guest]

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?

 

[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

 

[Guest]

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.???
--

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

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?

 

[David Biddulph]

You may have intended minutes and seconds, but by default Excel will treat
12:34 as 12h 34m, rather than 12m 34s.
Even 43:21 is treated as 43:21:00, thence 1d 19h 21m

You can check by formatting as [h]:mm:ss, and if you want to change the data
you can divide by 60 using Edit/Paste Special/ Divide, in a similar way to
adding zero in the previous message.
If you want to put numbers in from the keyboard to enter 12m 34s, you'll
need to enter 0:12:34, or 12:34.0
--
David Biddulph

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.???
--

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

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?