Using IF function with times/durations

[yahozna]

I'm doing a spreadsheet for a school sportsday - event times are recorded as mm:ss.00

I would like to be able to highlight if a school record is acheived in a seperate cell with the words "school record". I can't work out how to do a formula based on for example if cell B4 duration is less than 00:21.25 then "school record"

 

[DauntingGecko]

change A3 to the cell with the school record in it ("00:00:21")

=IF(B4< A3,"School record","")

 

[yahozna]

I've tried putting the school record in a seperate cell (for example E4) but using IF B4<E4 doesn't seem to work.
I thought that this must be because B4 is a duration/time rather than a number.

 

[DauntingGecko]

=IF(B4< TIME(0,0,21),"School record","")

 

[yahozna]

I really need minutes, seconds, tenths/hundreths rather than hour, minute, seconds. Is this possible?

 

[barry houdini]

If you have the record time in E4 then this works for me

=IF(B4< E4,"School record","")

although that will also show "School record" if B4 is blank so you might want to avoid that by modifying as follows:

=IF(B4="","",IF(B4< E4,"School record",""))

 

[yahozna]

If you have the record time in E4 then this works for me

=IF(B4< E4,"School record","")

although that will also show "School record" if B4 is blank so you might want to avoid that by modifying as follows:

=IF(B4="","",IF(B4< E4,"School record",""))

Thanks that works fine!! Now how could I check a range B4:B11 against the time in E4 (as there are 8 competitors in the event)

Thanks so much for your help - as you can see I'm a bit of a novice in Excel!

 

[DauntingGecko]

One formula per cell (competitor).

 

[barry houdini]

I assume you are putting the formula in row 4 then copying down so if you put a $ into E$4 that "fixes" the reference, e.g. in C4 copied down

=IF(B4="","",IF(B4< E$4,"School record",""))

Of course if more than one competitor beats the existing record that will show them all as "School record" so if you only want the winner(s) then change to

=IF(B4="","",IF(AND(B4< E$4,B4=MIN(B$4:B$11)),"School record",""))

 

[yahozna]

I assume you are putting the formula in row 4 then copying down so if you put a $ into E$4 that "fixes" the reference, e.g. in C4 copied down

=IF(B4="","",IF(B4< E$4,"School record",""))

Of course if more than one competitor beats the existing record that will show them all as "School record" so if you only want the winner(s) then change to

=IF(B4="","",IF(AND(B4< E$4,B4=MIN(B$4:B$11)),"School record",""))

I actually wanted to put the formula in just 1 cell but referencing the 8 competitors times. I know that this is probably overcomplicating things, but if its possible I'd love to know how.