Help looking up values!!
 

Hi all,
Hopefully I can explain this well enough to get some help. I have a
dataset of whale dive data that contains four columns. The first
column is time of day, the second is depth, and the third and fourth
are latitutiude and longitude. I am trying to look up the time of day
when a certain depth is first reached and when it is last reached.
Essentially I want to determine when a whale has reached it's maximum
dive depth and when it begins to surface. Is this making sense?
Here is an example (lat and long are no important, so I've left them
out) assuming I want:
12:01 20
12:02 30
12:03 40
12:04 45
12:05 46
12:06 40
12:07 39
12:08 30
So, in this case, I would want a formula (or two) that would tell me
the whale reached 40 at 12:03 and did not go shallower than that until
12:07. Get it?
HELP!!!


--
akbreezo
------------------------------------------------------------------------
akbreezo's Profile: http://www.excelforum.com/member.php...o&userid=24312
View this thread: http://www.excelforum.com/showthread...hreadid=379187

It is not real clear, but this might get you going in the right direction:

=OFFSET(D1,MATCH(MAX(D2:D4),D2:D4,0)+2,0)

It looks for the maximum number and offsets this by 2 rows

"akbreezo" wrote:


Hi all,
Hopefully I can explain this well enough to get some help. I have a
dataset of whale dive data that contains four columns. The first
column is time of day, the second is depth, and the third and fourth
are latitutiude and longitude. I am trying to look up the time of day
when a certain depth is first reached and when it is last reached.
Essentially I want to determine when a whale has reached it's maximum
dive depth and when it begins to surface. Is this making sense?
Here is an example (lat and long are no important, so I've left them
out) assuming I want:
12:01 20
12:02 30
12:03 40
12:04 45
12:05 46
12:06 40
12:07 39
12:08 30
So, in this case, I would want a formula (or two) that would tell me
the whale reached 40 at 12:03 and did not go shallower than that until
12:07. Get it?
HELP!!!


--
akbreezo
------------------------------------------------------------------------
akbreezo's Profile: http://www.excelforum.com/member.php...o&userid=24312
View this thread: http://www.excelforum.com/showthread...hreadid=379187

Hi
I would either sort by depth and then time and look at data that way or
write following code enter depth required into cell A1 and time data entered
a2 onwards depth data b2 onwards

sub macrodepth()
mydepth = Range("a1")
Columns("B:B").Select
Selection.Find(What:=mydepth, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
ActiveCell.Select
mytime1 = ActiveCell.Offset(0, -1)
Do Until ActiveCell < mydepth
If ActiveCell < mydepth Then
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
mytime2 = ActiveCell.Offset(0, -1)
Range("b1") = mytime1
Range("b1:c1").NumberFormat = "h:mm"
Range("c1") = mytime2
End Sub
i tried formulas but lots of variables
if enter depth in a1
=index(a2:b9,match(a1,b2:b9,0),1) returns 12:03
=index(a2:b9,match(a1-1,b2:b9,0),1) returns 12:07
but presumes next depth is 39
the macros finds where less than 40
hope this helps
Tina



"akbreezo" wrote:


Hi all,
Hopefully I can explain this well enough to get some help. I have a
dataset of whale dive data that contains four columns. The first
column is time of day, the second is depth, and the third and fourth
are latitutiude and longitude. I am trying to look up the time of day
when a certain depth is first reached and when it is last reached.
Essentially I want to determine when a whale has reached it's maximum
dive depth and when it begins to surface. Is this making sense?
Here is an example (lat and long are no important, so I've left them
out) assuming I want:
12:01 20
12:02 30
12:03 40
12:04 45
12:05 46
12:06 40
12:07 39
12:08 30
So, in this case, I would want a formula (or two) that would tell me
the whale reached 40 at 12:03 and did not go shallower than that until
12:07. Get it?
HELP!!!


--
akbreezo
------------------------------------------------------------------------
akbreezo's Profile: http://www.excelforum.com/member.php...o&userid=24312
View this thread: http://www.excelforum.com/showthread...hreadid=379187