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" wrote in message ... 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 Replace@mailinator with @tiscali.co.uk "Ray Wright" wrote in message ... 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" wrote in message ... 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 |
All times are GMT +1. The time now is 09:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com