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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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))

ctrl>shift>enter (not just enter)
Highlight B2 & C2 and copy down as far as needed
 
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
 
DDD wrote...
....
The input data is not finite and varies day to day month to month. What I
Click to expand...

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? ....
Click to expand...

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$65536))

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.
 
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 care:

ddd
 
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 ?
Click to expand...
....

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.
 
Back
Top