hour of maximum

[Annika]

I am trying to create a formula, function, or macro to
determine the time at which a maximum occurs. So far I've
been using VBA, but if there is an easier way, please let
me know. Details below:

I use long colums of hourly data. One column will contain
the date and time in the yyyy/mm/dd hh:mm format. For
example, part of the column of data mught look like this:
2001/12/25 17:00
2001/12/25 18:00
2001/12/25 19:00
2001/12/25 20:00
2001/12/25 21:00
2001/12/25 22:00
2001/12/25 23:00
2001/12/26 0:00
2001/12/26 1:00
2001/12/26 2:00


There are 24 entries for each day.

Adjacent to that will be a column (defined by the user) of
hourly data, with a number format containing only one
decimal place. For example, part of the column of data
mught look like this:

53.2
41.9
22.0
13.5
17.2

This is the column that is processed.


This is my piece of code for finding out which column of
data he/she would like to process:

'Ask user for column of source data
Column = InputBox("Which column of hourly data would you
like to process?", "Column")

This is my piece of code for finding out where the user is
storing the date in the yyyy/mm/dd hh:mm format:

'Ask user for column containing source date data
DateColumn = InputBox("Which column is the date and hour
recorded in?", "Date Column")




This is my piece of code for finding out the maximum for
each day:

'Formula for daily maximum

With ActiveCell.Offset(RowOffset:=i + 1, ColumnOffset:=1)
.Value = "=MAX(" & HrlySheet & "!" & Column &
(StartRow + (i * 24)) & ":" & Column & (StartRow + (i *
24) + 23) & ")"
.NumberFormat = "0.0"
End With

My question is: how can I have a column of data,
portraying the hour at which the maximum occured? I know
that you can use the "hour()" function for some things,
but how can I get the hour the maximum is occuring at, if
the date and hour is in another column?
Help!

 

[Ron Rosenfeld]

To display the date and time of the maximum entry (assumes date/time is in
column A and values are in column B):

=OFFSET(A1,-1+MATCH(MAX(B1:B10),B1:B10,0),0)

To display the hour of the maximum for any given date, *array-enter*:

=OFFSET($A$1,-1+MATCH(MAX((INT($A$1:$A$10)=E2)*$B$1:$B$10),$B$1:$B$10,0),0)

E2 contains the date that you're interested in.
To *array-enter* a formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.

If you have a column of dates of interest in E2:En, such as:

12/26/2001
12/27/2001

 

[Dan E]

Annika,
If both columns A & B (Date and Data) are on Sheet2 it should look like
=HOUR(INDEX(Sheet2!A1:A10,MATCH(MAX(Sheet2!B1:B10),Sheet2!B1:B10,0),1))
or all three positions.

Dan E

 

[Annika]

Thanks again, Dan. You're my hero! Talk about making
someone's day!