Thursday, October 18, 2012

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:

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,

2 comments:

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

    ReplyDelete
  2. I 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