I am having difficulty finding formulas to do the following: Max, Min with IT extracted outputs.

Simple Example:

Date(A) Time(B) Order(C)
04/17/2012 09:29:38 10
04/16/2012 09:34:24 20
04/15/2012 09:27:41 30
04/14/2012 08:14:47 40
04/13/2012 08:43:52 50

The file is a data extract from IT and the formatting keeps zeros. Also to get a simple MAX formula to work for column "Time" I had to use: MAX(TIMEVALUE(B:B))
The other columns are formatted general; Time reads hh:mm:ss
In case this is a factor I'm using Excel 2007

The problem is I need the max values to have conditions of Order"=10", Order"<=30" or some equivalant as I go along in the spreadsheet

Can anyone please help me figure this out?

Any assistance is greatly appreciated in advance as I have been stuck on this aspect of the work project for over a week.

"methodikal" wrote:
Simple Example:
Date(A) Time(B) Order(C)
04/17/2012 09:29:38 10
04/16/2012 09:34:24 20
04/15/2012 09:27:41 30
04/14/2012 08:14:47 40
04/13/2012 08:43:52 50
[....]
to get a simple MAX formula to work for column
"Time" I had to use: MAX(TIMEVALUE(B:B))
The other columns are formatted general; Time
reads hh:mm:ss

First, if that formula worked, I suspect it worked only by coincidence.

I presume you entered it into a cell parallel to a value in column B (and it
returned that value, which might have been the max only by coincidence), and
you entered it as a normal formula, pressing just Enter instead of
ctrl+shift+Enter (array-entered). I doubt that have "1 million" (1,048,576)
values in column B.

The point is: the formula is wrong. Although XL2007 and later does permit
the use of B:B in that context, TIMEVALUE returns a #VALUE error if any cell
does not contain text that looks like some recognized form of time; in
particular, if any cell is empty.

By the way, it is "bad form" to use column references like B:B especially in
XL2007 and later. In most contexts, it causes Excel to process all "1
million" cells in the column; and it results in an array of "1 million"
entries (4MB of memory for Excel time).

It would behoove you to choose a reason limited range, e.g. B2:B10000.

-----

Second, the fact that you had to use TIMEVALUE suggests that the contents of
column B are treated as text, not Excel numeric time despite the cell
format.

It would behoove you to correct that problem. Ideally, figure out why the
"data extract" (imported data?) was treated as text in the first place.
Perhaps you should import it differently. But at the very least, try to use
Text To Columns to convert the data to actual Excel numeric time.

For now, the following will assume that column B contains text "time".


"methodikal" wrote:
The problem is I need the max values to have conditions
of Order"=10", Order"<=30" [...].
Can anyone please help me figure this out?

Use the following __array-entered__ formula (press ctrl+shift+Enter instead
of just Enter):

=MAX(IF(C2:C10000=10,IF(C2:C10000<=30,TIMEVALUE(B 2:B10000))))

I presume that if the criteria for column C are met, the corresponding cell
in column B is not empty. So TIMEVALUE should be happy.

An array-entered formula will appear in the Formula Bar surrounded by curly
braces, e.g. {=formula}. You cannot type the curly braces yourself; that is
just Excel's way of distinguishing array-entered formulas from normal
formulas. If you need to change the formula, remember to press
ctrl+shift+Enter again.

PS: When you correct the data in column B so that it is Excel numeric time,
change the formula to the following array-entered formula:

=MAX(IF(C2:C10000=10,IF(C2:C10000<=30,B2:B10000)) )