Time format and Formula problems

**bsacco** · 04-13-2010, 03:14 PM

I'm a Track & Field coach.

I have the times of my middle & long distance runners entered into EXCEL but I'm have two different problems with them.

1) the TIME formatting of the CELL itself.

2) when creating a formula to find the lowest time in an array I get an error.

For example:

I'm attempting to find the LOWEST time value in a ROW.

I have (3) times entered into (3) cells consecutively. They are A1, B1 and C1

A1 = 2:04.22
B1 = 1:59.69
C1 = 2:02.00

I tried using a FORMULA to find the lowest time in the array by creating =MINA(A1:C1) but it returns "0" because of the formatting the time issue.

Anyone have any suggestions on what specific TIME CELL FORMAT I should use?

And finally, anyone have any suggestions on what TIME CELL FORMAT I should use on the actual formula?

Go easy...I'm a newbie...

thanks,
bob

**zbor** · 04-13-2010, 03:18 PM

You can try just =MIN(A1:C1) function and format cell as time

**NBVC** · 04-13-2010, 03:38 PM

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.

**DonkeyOte** · 04-13-2010, 04:12 PM

Per NBVC's post - ask a Mod to move your post to an appropriate thread.

Regards your question... as outlined by zbor use MIN not MINA - the two are quite different.

However, MINA(A1:C1), based on your values should still return 1:59.69 given all cells contain a valid time entry (m:ss.ms) - ensure cell containing formula is format as Time of course.

If all cells are populated, the formula cell is formatted as time yet the MIN result is 0:00.00 then implication is such that the entries are text at which point you should either:

a) coerce them to numbers (advised) and persist with MIN

b) use a MIN Array (least preferable)

=MIN(IF(ISNUMBER(--A1:C1),--A1:C1))
confirmed with CTRL + SHIFT + ENTER

**bsacco** · 04-13-2010, 10:06 PM

Thanks but I'm still getting 00:00.00 as the result.

I formatted the time entry cells AND the cell containing the formula as mm:ss.00. ...but I still get 00:00.00 as the result.

why does Excel act this way?

**DonkeyOte** · 04-14-2010, 03:11 AM

Formatting the cells as Time if they contain Text will have no effect - eg if A1 = "Apple" - formatting A1 as a Number does not make "Apple" numeric.

To coerce the input values (A1:C1) from text to number - if that's your preference - enter 1 into a blank cell, copy it, highlight all entries and run Paste Special -> Operation: Multiply.

If you prefer to leave the originals unaltered try the MIN Array as suggested.

I would suggest posting a sample file - first though please endeavour to have your thread moved c/o a Moderator.