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!

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!