Help looking up values!!

[akbreezo]

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!!!

[Damon Longworth]

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
>
>

[tina]

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
>
>