S
snick
I am using the DMAX function to find the maximum data value within a
certain range of dates. I
now need to find the date associated with that maximum value. I am
dealing with streamflow data
in 5-minute intervals, so there are many peaks in the data. I am looking
at specific events by
specifying a time range to search in. I have two columns, one column of
dates and times
(mm/dd/yy hh:mm) and one column of water depth.
I have tried DGET (e.g., DGET(R104:T65000, "datetime",R2:U3)) to return
the date assocuated with
the maxium found by DMAX, but the maximum value may be found multiple
times within the time
period (there are often multiple data points at the peak value).
I have also tried MATCH and OFFSET (e.g.,
OFFSET(H$104,MATCH(G4,H105:H55000,0),-1) where G4 is
the maximum value returned from DMAX), but this returns the first
occurance (in the whole
column) of the value in G4, and I need the date of the occurance the
maximum value in a
specified range of dates.
I would very much appreciate any insights into how to return a cell
reference to the result of
DMAX and the date associated with it (same row, just one column over).
I have looked at both the INDEX and ADDRESS functions, but my
understanding is that you need to know the row number to use these
functions...and that's what I'm trying to find. All I know is the value
and the range of dates - I would like to match the date of the value
returned by DMAX.
thank you!
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **
certain range of dates. I
now need to find the date associated with that maximum value. I am
dealing with streamflow data
in 5-minute intervals, so there are many peaks in the data. I am looking
at specific events by
specifying a time range to search in. I have two columns, one column of
dates and times
(mm/dd/yy hh:mm) and one column of water depth.
I have tried DGET (e.g., DGET(R104:T65000, "datetime",R2:U3)) to return
the date assocuated with
the maxium found by DMAX, but the maximum value may be found multiple
times within the time
period (there are often multiple data points at the peak value).
I have also tried MATCH and OFFSET (e.g.,
OFFSET(H$104,MATCH(G4,H105:H55000,0),-1) where G4 is
the maximum value returned from DMAX), but this returns the first
occurance (in the whole
column) of the value in G4, and I need the date of the occurance the
maximum value in a
specified range of dates.
I would very much appreciate any insights into how to return a cell
reference to the result of
DMAX and the date associated with it (same row, just one column over).
I have looked at both the INDEX and ADDRESS functions, but my
understanding is that you need to know the row number to use these
functions...and that's what I'm trying to find. All I know is the value
and the range of dates - I would like to match the date of the value
returned by DMAX.
thank you!
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **