Monthly MAX

[Sven]

Hi Gang
I have a sheet with numerous hourly readings across across each row. I am
trying to create a summary sheet, that will look at each month, find the max
of col F and copy the entire row to the summary sheet.
Thanks for your help
Sven

 

[Paul Corrado]

Sven,

For any one months worth of data, you can put this formula in column A and
copy across. Just change the $A$1 $F2:$F4 references to include the proper
worksheet reference and adjust the F2:F2 range as needed.

=OFFSET($A$1,MATCH(MAX($F2:$F4),$F2:$F4),COLUMN()-1)

It can most likely be modified to pick up the data for any one month, but
without knowing how your monthly data is arranged I can't say for sure.

 

[Sven]

A1:A24 dd/yy/mmmm
B1:B24 1-24 (Hour)
C1:C24 Data point1
through
Q1:Q24 Data Point 15
Copied down 24 rows each day for the year

Summary sheet
Row 1 is the data points corresponding to the max of Col F for Jan
Row 2 is the data points corresponding to the max of Col F for Feb
and so on

Thanks!

 

[Paul Corrado]

I think this Array formula will work. (Press CTRL+SHIFT+ENTER and {}'s will
appear around the formula.) Adjust ranges as necessary and change the =4 to
a cell reference with the month of data you wish to capture.

=OFFSET($A$1,MATCH(MAX(IF(MONTH($A$2:$A$16)=4,$F$2:$F$16)),$F$2:F$16,0),COLU
MN()-1)