**Question:**

( by mykarz )

I've been trying every which way to get something that would seem simple enough to do, to actually work.

I want to see the minimum time of a series of times using the m:ss.xxx format, as well as see the maximum of the same set, yet whenever I run a "min"/"mina" or "max"/"maxa" function, I always get 0 as a return. So this means that my inputs are not viewed as numbers, but as text?

Any help would be appreciated! :)

**Solution:**

First of all, format your series of times as "Plain Text".

Have a look at the following link to format range as a "Plain text":

http://igoogledrive.blogspot.

Now, have a look at the following screenshot:

Put the following formula in the cell "D5" for the min value:

=IFERROR(QUERY(ArrayFormula(A:A);"select Col1 where Col1 <> '' order by Col1 limit 1"))

Put the following formula in the cell "D6" for the max value:

=IFERROR(QUERY(ArrayFormula(A:A);"select Col1 where Col1 <> '' order by Col1 desc limit 1"))

In the above formulas replace the range ( or column ) "A:A" according to your requirement.

Or alternatively you can also try the following formulas:

For min value:

=IFERROR(QUERY(A:A;"select A where A <> '' order by A limit 1"))
For max value:

=IFERROR(QUERY(A:A;"select A where A <> '' order by A desc limit 1"))I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,

Kishan,

Is there a formula that will return which cell has the min/max value, i.e. A9 and A1 for this example?

ReplyDeleteI tried this with an array of times in the format "11:00 AM" and it doesn't seem to work. Is there a way to do this that I'm not seeing?

ReplyDelete