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"

 

[barry houdini]

OK, if you just want to find out if any of the times are less than E4 you could just compare E4 to the smallest (i.e. winning) time

=IF(MIN(B4:B11)< E4,"School record","")

 

[yahozna]

OK, if you just want to find out if any of the times are less than E4 you could just compare E4 to the smallest (i.e. winning) time

=IF(MIN(B4:B11)< E4,"School record","")

Is there a way of getting this formula to ignore blank cells? It would then be perfect!!

 

[DauntingGecko]

Grr....it won't let me type the answer...

 

[DauntingGecko]

God knows why I can't reply with an answer. Only half my formula gets posted!

=IF(COUNTBLANK(B4:B11)=8,"",IF(MIN(B4:B11) < E4,"School record",""))

 

[yahozna]

God knows why I can't reply with an answer. Only half my formula gets posted!

=IF(COUNTBLANK(B4:B11)=8,"",IF(MIN(B4:B11) < E4,"School record",""))

I'm afraid that doesn't work. If all the cells are blank (before the event) then it shows "School record".

Assuming the school record is 00:17.15 (E4)

I typed 00:17.20 into B4 - "School record" disappears
I typed 00:17.10 into B5 - still no "School record"
I typed 00:17.15 into B6 - "School record" reappears!

Thanks again for helping out!!

 

[DauntingGecko]

Works fine? Just checked it from scratch...

 

[yahozna]

Works fine? Just checked it from scratch...

Yes you're right. I'd actually miscounted the number of competitors in the event!!!!!!!!! Brain freeze...

Thanks for that - I really appreciate your assistance. Let the games begin!!!