Index - Offset - Match Issues

I have a large dataset (about 55,000 records) that consists of instrument
readings taken at different times on different days (between 24 - 40
readings per day). The data is in the format: Col A - dates; Col B - times
(24 hour clock format); and Col C - instrument readings.



I want to extract (on another worksheet) the maximum instrument reading per
day and the time of that reading (Col A - dates; Col. B - max reading for
the date in Col A; Col. C - time of that reading). I have successfully
extracted the maximum instrument reading with the array formula:

{=MAX(IF(Sheet1!A1:A55000=A1,Sheet1!C1:C55000))}



However, I have not been able to extract the time of the maximum reading.
Is the solution come combination of INDEX and /or MATCH and/or OFFSET? Any
help appreciated.



I am using XL2002.



Ray Wright

Not tested very much but here is a start. Note that I used 20 rows only for
my experiment, so change as needed
A1: a date value as you specify
B1: =MAX(IF(Sheet1!A1:A20=A1,Sheet1!C1:C20)) as you have
B2:=MATCH(B1,Sheet1!C1:C20,0) will find the row with the same value as the
MAX. However, a reading may not be unique.
So in D1 of Sheet1 use formula =A1&C1 and copy down to end of column
(combine date and reading)
Now in C1 (Sheet2) use =MATCH(A1&B1,Sheet1!E1:E20,0) this returns the (A1 is
date, B1 is reading)
This tells us the row
For the Time use =INDEX(Sheet1!B1:B20,B2)


--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Ray Wright" <raycyn.wright@prodigy.net> wrote in message
news:uoY%e.51$HA.42@newssvr22.news.prodigy.net...
>I have a large dataset (about 55,000 records) that consists of instrument
>readings taken at different times on different days (between 24 - 40
>readings per day). The data is in the format: Col A - dates; Col B - times
>(24 hour clock format); and Col C - instrument readings.
>
>
>
> I want to extract (on another worksheet) the maximum instrument reading
> per day and the time of that reading (Col A - dates; Col. B - max reading
> for the date in Col A; Col. C - time of that reading). I have
> successfully extracted the maximum instrument reading with the array
> formula:
>
> {=MAX(IF(Sheet1!A1:A55000=A1,Sheet1!C1:C55000))}
>
>
>
> However, I have not been able to extract the time of the maximum reading.
> Is the solution come combination of INDEX and /or MATCH and/or OFFSET?
> Any help appreciated.
>
>
>
> I am using XL2002.
>
>
>
> Ray Wright
>
>
>
>

I haven't done much testing of it but would the array formulas:

=MAX((Sheet3!A1:A55000=A1)*Sheet3!C1:C55000)

and:

=LOOKUP(MAX((Sheet1!A1:A55000=A1)*Sheet1!C1:C55000),Sheet1!B1:B55000)

work for the maximum reading and time respectively.

Note that if the formulas are in the same sheet as the data then the Sheet1!
is not required.

For the archives enter both formulas with Ctrl + Shift + Enter

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Ray Wright" <raycyn.wright@prodigy.net> wrote in message
news:uoY%e.51$HA.42@newssvr22.news.prodigy.net...
>I have a large dataset (about 55,000 records) that consists of instrument
>readings taken at different times on different days (between 24 - 40
>readings per day). The data is in the format: Col A - dates; Col B - times
>(24 hour clock format); and Col C - instrument readings.
>
>
>
> I want to extract (on another worksheet) the maximum instrument reading
> per day and the time of that reading (Col A - dates; Col. B - max reading
> for the date in Col A; Col. C - time of that reading). I have
> successfully extracted the maximum instrument reading with the array
> formula:
>
> {=MAX(IF(Sheet1!A1:A55000=A1,Sheet1!C1:C55000))}
>
>
>
> However, I have not been able to extract the time of the maximum reading.
> Is the solution come combination of INDEX and /or MATCH and/or OFFSET?
> Any help appreciated.
>
>
>
> I am using XL2002.
>
>
>
> Ray Wright
>
>
>
>

Sort your data first by date (ascending) and then by instrument value
(descending).
At this point the maximum instrument value is the first record in each group
of dates.

Now a simple VLOOKUP() can be used to pull both the time and maximum
instrument value.

Have a good day
--
Gary''s Student


"Sandy Mann" wrote:

> I haven't done much testing of it but would the array formulas:
>
> =MAX((Sheet3!A1:A55000=A1)*Sheet3!C1:C55000)
>
> and:
>
> =LOOKUP(MAX((Sheet1!A1:A55000=A1)*Sheet1!C1:C55000),Sheet1!B1:B55000)
>
> work for the maximum reading and time respectively.
>
> Note that if the formulas are in the same sheet as the data then the Sheet1!
> is not required.
>
> For the archives enter both formulas with Ctrl + Shift + Enter
>
> --
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
> "Ray Wright" <raycyn.wright@prodigy.net> wrote in message
> news:uoY%e.51$HA.42@newssvr22.news.prodigy.net...
> >I have a large dataset (about 55,000 records) that consists of instrument
> >readings taken at different times on different days (between 24 - 40
> >readings per day). The data is in the format: Col A - dates; Col B - times
> >(24 hour clock format); and Col C - instrument readings.
> >
> >
> >
> > I want to extract (on another worksheet) the maximum instrument reading
> > per day and the time of that reading (Col A - dates; Col. B - max reading
> > for the date in Col A; Col. C - time of that reading). I have
> > successfully extracted the maximum instrument reading with the array
> > formula:
> >
> > {=MAX(IF(Sheet1!A1:A55000=A1,Sheet1!C1:C55000))}
> >
> >
> >
> > However, I have not been able to extract the time of the maximum reading.
> > Is the solution come combination of INDEX and /or MATCH and/or OFFSET?
> > Any help appreciated.
> >
> >
> >
> > I am using XL2002.
> >
> >
> >
> > Ray Wright
> >
> >
> >
> >
>
>
>

Thank you, Barnard, Sandy., and Gary's Student for expanding my knowldedge
of Excel. I was able to address my problem successfully using a combination
of Index and Match coupled with the briilliant insight of combining the date
and reading (thank you Bernard!). You al lare a wonderful resource.

Thanks again for your helopful suggestions.

Ray Wright

"Ray Wright" <raycyn.wright@prodigy.net> wrote in message
news:uoY%e.51$HA.42@newssvr22.news.prodigy.net...
>I have a large dataset (about 55,000 records) that consists of instrument
>readings taken at different times on different days (between 24 - 40
>readings per day). The data is in the format: Col A - dates; Col B - times
>(24 hour clock format); and Col C - instrument readings.
>
>
>
> I want to extract (on another worksheet) the maximum instrument reading
> per day and the time of that reading (Col A - dates; Col. B - max reading
> for the date in Col A; Col. C - time of that reading). I have
> successfully extracted the maximum instrument reading with the array
> formula:
>
> {=MAX(IF(Sheet1!A1:A55000=A1,Sheet1!C1:C55000))}
>
>
>
> However, I have not been able to extract the time of the maximum reading.
> Is the solution come combination of INDEX and /or MATCH and/or OFFSET?
> Any help appreciated.
>
>
>
> I am using XL2002.
>
>
>
> Ray Wright
>
>
>
>