Google Spreadsheet Formula to return a min and max time value

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.in/2012/09/Format-range-as-Plain-text-in-Google-Spreadsheet.html

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:

http://igoogledrive.blogspot.in/p/paid-projects.html

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,

iGoogleDrive