returning cell reference

[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).

you can email me at nsbates(remove this)(at)princeton.edu
thank you!
-Naomi

 

[agarwaldvk]

May be you can try this :-

=INDEX(A1:B9,MATCH(LARGE(B1:B9,1),B1:B9,0),1)

This, on the assumption that the range A1:A9 contains your date tim
range and the range B1:B9 contains the data points, will return th
date for the largest data point in the selected range.

You may need to alter your data range depending upon the date &/or tim
range that you may be interested in. However, when there are multipl
largest data points in the specified range, only the first date wit
the largest data point would be returned.


Hope this helps!


Best regards



Deepak Agarwa

 

[Aladin Akyurek]

See my reply at:

http://www.mrexcel.com/board2/viewtopic.php?t=102591

 

[Guest]

Thanks so much!

-N