Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Finding Max values
Hello
Repost of early question... My data sheet is laid out as follows with hourly readings: 24/7/365 Date Hour Temp RelHum WindSpeed On my summary sheet, for each month I would like to find the line with the max temp and copy the rest of the data from that line Date Hour Temp RelHum WindSpeed What formula would I use to accomplish this? Thanks! |
#2
|
|||
|
|||
Assume source table is in Sheet1, in A1:E10
data from row2 to row10 Date Hour Temp RelHum WindSpeed 30-May-05 H1 87 RH1 WS1 31-May-05 H2 84 RH2 WS2 1-Jun-05 H3 47 RH3 WS3 2-Jun-05 H4 53 RH4 WS4 3-Jun-05 H5 51 RH5 WS5 4-Jun-05 H6 37 RH6 WS6 5-Jun-05 H7 59 RH7 WS7 6-Jun-05 H8 13 RH8 WS8 7-Jun-05 H9 72 RH9 WS9 (Temp is in col C) In Sheet2: With the headers below in A1:F1 : Mth Date Hour Temp RelHum WindSpeed and the months* listed in A2 down, e.g.: May-05 Jun-05 etc *1st of month actual dates formatted as: mmm-yy Put in the formula bar for B2 and array-enter, i.e press CTRL+SHIFT+ENTER: =INDEX(Sheet1!A$2:A$10,MATCH(MAX(IF(MONTH(Sheet1!$ A$2:$A$10)=MONTH($A2),Shee t1!$C$2:$C$10)),Sheet1!$C$2:$C$10,0)) Copy B2 across to F2, fill down to populate the grid Format col B as dates For the sample data, you'll get: Mth Date Hour Temp RelHum WindSpeed May-05 30-May-05 H1 87 RH1 WS1 Jun-05 7-Jun-05 H9 72 RH9 WS9 Note that if there are tied max temps within any one month, only the first row (the lower row #) would be returned .. Adapt the ranges to suit .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Al" wrote in message ... Hello Repost of early question... My data sheet is laid out as follows with hourly readings: 24/7/365 Date Hour Temp RelHum WindSpeed On my summary sheet, for each month I would like to find the line with the max temp and copy the rest of the data from that line Date Hour Temp RelHum WindSpeed What formula would I use to accomplish this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Values With more than 2 decimal Places | Excel Discussion (Misc queries) | |||
Finding unique values with Criterias | Excel Discussion (Misc queries) | |||
Finding Unique Values in Column | Excel Discussion (Misc queries) | |||
Finding Values in a "Matrix" | Excel Discussion (Misc queries) | |||
Finding the max of a column where values less than a specified number | Excel Worksheet Functions |