time formated cells stay blank if zero

I I am trying to create a spreadsheet to track my kids swimming times, I have created a basic version of it and attached it for viewing.

I want a formula to leave cells blank if they are showing "00:00.00" (so is blank or = 0) but if they are showing a time then do the calculation.

Example col B current cal show =MIN(J5:XFD5) for row 5 but as the selected range has no times in put 00:00.00 is returned.

I have tried if statements (=if"","",) but I get errors I think because its time formatted.

the cells are formatted as custom MM:SS.00 as we never go into hours.

I also need a formula to leave the cells blank if they are negative numbers so I don’t get the ####### in the cell but I think this will be a slight change to the above if possible.

I know I can use conditional formatting to colour the text but this is not a great option as I would like to use condition formatting for other purposes and have run out of options on certain cell in the mail workbook i have created so I really need a formula if possible.

any help/ideas?

Originally Posted by tlafferty

When doing calculations, it's not advisable to use a formula to force a text value of blank as this can cause errors when adding, subtracting etc. It's better to leave a zero in place. Excel has options to not display a zero. Click the office button, then click Excel options, Advanced, Display options for this worksheet. Remove the check from show a zero in cells that have a zero value. Also, you can force a positive by surrounding the formula with ABS(your formula here).

Ok I understand the issue with forcing blank but is it possible to create a formula that does this with finding the minimun value of a range as in the book1? as although your option will work I am get frustrated with the formula.

not sure about the last bit "you can force a positive by surrounding the formula with ABS(your formula here)" can you explain this further maybe show me in the workbook and re-upload.

cheers

Darren

Originally Posted by tlafferty

Hi there,
Sorry about the delay in posting back. After looking at what you're trying to do, I decided to turn off the display of zeros:

Please Login or Register  to view this content.

Also, you asked me to explain the ABS formula: all it does is finds the absolute value. If the value entered is negative, it returns a positive (multiplies by negative one), if the value is positive, it remains positive.

See attached workbook.

Thanks for the reply

Ok Think I am getting to grips with the abs bit, I will play when I have more time.

I have been playing again and I am struggling with the formula in cell B5, this where all my confusion started but then I went off at a tangent looking at other stuff too, I think the formula shows what I am trying to achieve but its retuning a #value.
I’m not sure if I need it now but would be nice to know if its possible?
If it is what am I doing wrong?
If not is there a reason?

Cheers

Darren