Index - Offset - Match Issues

[Ray Wright]

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

 

[Bernard Liengme]

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)

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Guest]

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

 

[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