Easy for the experts: Lookup,index,match....
 

Good Day:

Data Input is as follows:

A B C
Date Time Value
09/01/2006 19:25:37 8.89
09/01/2006 19:29:37 8.23
09/01/2006 19:33:37 8.14
09/01/2006 19:37:37 8.22
. . .
. . .


The data set is quite large and is continuous for one month of data. I would
like to get an output that looks like the following (on a sepearate
worksheet):

A B C
Date Min. Max.

09/01/2006 8.14 8.89
09/02/2006 6.54 9.76
09/03/2006 7.01 8.46
. . .
. . .

Thanks in advance for your assistance. DDD

Easy for the experts: Lookup,index,match....
 

On sheet 2

B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))
C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))

ctrlshiftenter (not just enter)
Highlight B2 & C2 and copy down as far as needed



"DDD" wrote:

Good Day:

Data Input is as follows:

A B C
Date Time Value
09/01/2006 19:25:37 8.89
09/01/2006 19:29:37 8.23
09/01/2006 19:33:37 8.14
09/01/2006 19:37:37 8.22
. . .
. . .


The data set is quite large and is continuous for one month of data. I would
like to get an output that looks like the following (on a sepearate
worksheet):

A B C
Date Min. Max.

09/01/2006 8.14 8.89
09/02/2006 6.54 9.76
09/03/2006 7.01 8.46
. . .
. . .

Thanks in advance for your assistance. DDD

Easy for the experts: Lookup,index,match....
 

Thanks mama, but I guess I was not clear enough.

The input data is not finite and varies day to day month to month. What I
was looking for was how to search all of the 09/01/06 entries (between 300
and 1000 entries) for a max and min, then write that date and it's
corresponding min/max values - then continue to the next date in the data
09/02/06 and search all that date for min/max - write that date and the
min/max --- continue until the data runs out about 10K to 13K entries per
month... Does that make sense?

Take care

"Teethless mama" wrote:

On sheet 2

B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))
C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))

ctrlshiftenter (not just enter)
Highlight B2 & C2 and copy down as far as needed



"DDD" wrote:

Good Day:

Data Input is as follows:

A B C
Date Time Value
09/01/2006 19:25:37 8.89
09/01/2006 19:29:37 8.23
09/01/2006 19:33:37 8.14
09/01/2006 19:37:37 8.22
. . .
. . .


The data set is quite large and is continuous for one month of data. I would
like to get an output that looks like the following (on a sepearate
worksheet):

A B C
Date Min. Max.

09/01/2006 8.14 8.89
09/02/2006 6.54 9.76
09/03/2006 7.01 8.46
. . .
. . .

Thanks in advance for your assistance. DDD

Easy for the experts: Lookup,index,match....
 

DDD wrote...
....
The input data is not finite and varies day to day month to month. What I

Maybe your data isn't 'finite' theoretically, but you can only fit a
finite amount into any Excel worksheet. Perhaps you mean it could fill
an entire column from row 2 down to row 65536. I'll assume that's what
you meant.

was looking for was how to search all of the 09/01/06 entries (between 300
and 1000 entries) for a max and min, then write that date and it's
corresponding min/max values - then continue to the next date in the data
09/02/06 and search all that date for min/max - write that date and the
min/max --- continue until the data runs out about 10K to 13K entries per
month... Does that make sense?
....
"Teethless mama" wrote:
B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))
C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))

You could use the suggested approach, just adjust the range references,
e.g.,

B2:
=MIN(IF(Sheet1!$A$2:$A$65536=$A2,Sheet1!$C$2:$C$65 536))

and similarly for the MAX in C2.

Adjust the range references in the formulas above as needed. Since you
haven't specified *YOUR* actual ranges or worksheet names, that task is
left to YOU unless you provide those additional details.

Easy for the experts: Lookup,index,match....
 

Harlan:

That did the trick. Perfect...

Now, if I wanted to search sheet one (pH Readings) for all 09/01/06 values,
find the min and max and place the answer in sheet two (Min-Max) - DATE ,
MIN, MAX and then continue through all the dates available - could I not use
a lookup, or an Index/Match function ?

Take ca

ddd

"Harlan Grove" wrote:

DDD wrote...
....
The input data is not finite and varies day to day month to month. What I

Maybe your data isn't 'finite' theoretically, but you can only fit a
finite amount into any Excel worksheet. Perhaps you mean it could fill
an entire column from row 2 down to row 65536. I'll assume that's what
you meant.

was looking for was how to search all of the 09/01/06 entries (between 300
and 1000 entries) for a max and min, then write that date and it's
corresponding min/max values - then continue to the next date in the data
09/02/06 and search all that date for min/max - write that date and the
min/max --- continue until the data runs out about 10K to 13K entries per
month... Does that make sense?
....
"Teethless mama" wrote:
B2 =MIN(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))
C2 =MAX(IF(Sheet1!$A$2:$A$5=Sheet2!$A2,Sheet1!$C$2:$C $5))

You could use the suggested approach, just adjust the range references,
e.g.,

B2:
=MIN(IF(Sheet1!$A$2:$A$65536=$A2,Sheet1!$C$2:$C$65 536))

and similarly for the MAX in C2.

Adjust the range references in the formulas above as needed. Since you
haven't specified *YOUR* actual ranges or worksheet names, that task is
left to YOU unless you provide those additional details.

Easy for the experts: Lookup,index,match....
 

DDD wrote...
....
Now, if I wanted to search sheet one (pH Readings) for all 09/01/06 values,
find the min and max and place the answer in sheet two (Min-Max) - DATE ,
MIN, MAX and then continue through all the dates available - could I not use
a lookup, or an Index/Match function ?
....

You *could* use lookup or index/match calls, but there's nothing gained
by doing so. MIN/IF and MAX/IF are all you need. This assumes you've
already entered dates in your second worksheet. If so, then you use
those date entries in the second worksheet in the conditional terms
(first arguments) of the IF function calls to compare against the
entire column of dates in the first worksheet, and you use the value
range in the first worksheet as the second argument of the IF function
calls. This produces an array of values from the first worksheet
corresponding to the dates from the first worksheet that match the
given date from the second worksheet, and FALSE for nonmatching dates.
Enclosing MIN and MAX calls then return the minimum and maximum values
for the given date. No need for lookups.

Easy for the experts: Lookup,index,match....
 

Harlan:

Thank you so much for your help and advice.

Take care

ddd

"Harlan Grove" wrote:

DDD wrote...
....
Now, if I wanted to search sheet one (pH Readings) for all 09/01/06 values,
find the min and max and place the answer in sheet two (Min-Max) - DATE ,
MIN, MAX and then continue through all the dates available - could I not use
a lookup, or an Index/Match function ?
....

You *could* use lookup or index/match calls, but there's nothing gained
by doing so. MIN/IF and MAX/IF are all you need. This assumes you've
already entered dates in your second worksheet. If so, then you use
those date entries in the second worksheet in the conditional terms
(first arguments) of the IF function calls to compare against the
entire column of dates in the first worksheet, and you use the value
range in the first worksheet as the second argument of the IF function
calls. This produces an array of values from the first worksheet
corresponding to the dates from the first worksheet that match the
given date from the second worksheet, and FALSE for nonmatching dates.
Enclosing MIN and MAX calls then return the minimum and maximum values
for the given date. No need for lookups.