Need help looking up value based on criteria

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

[olasa]

I would recommend to make a Pivottable (see encl. zip-file)

Hope it helped
Ola Sandström


Zip-file: http://www.excelforum.com/attachment...tid=3502&stc=1
Videos about Pivottables: http://www.datapigtechnologies.com/ExcelMain.htm

[akbreezo]

Thank you. Will a pivot table work for large amounts of data? Can you create a pivot table based on named ranges?

[olasa]

Thanks, Yes, it should work with a large amount of data and Yes it will definitly work with Named ranges.

Ola Sandström

Just as an observation - I fliped a x-y chart, and I think it gives a good visual image. See encl.: http://www.excelforum.com/attachment...tid=3503&stc=1

[akbreezo]

The pivot table looks promising, but I am having trouble getting it to work. Let me provide a bit more information about my data and perhaps you can help.
I have a whole series of dives for a single whale. I have separated the data into dives and named the ranges, meaning A2:B190 is called dive_1 and A191:B286 is called dive_2 and so on. I would want to do what you have done with the pivot table for each of these named dives. The problem is when I place the name into the pivot table range, there are not any column headers, so the data does not lay out as it does in your example.
I have charted all of the dives, but I need to do the analysis concretely as well.
Thanks again!

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Ron Coderre]

Hi, Akbreezo

If your data is in columns A:B and you ranges are name Dive_1, Dive_2, etc, I think this will work for you:

1)Put the name of the range to query in Cell C1 (eg dive_1).
2)Put the depth to test in Cell C2 (eg 40).
3)Start time of test depth is in cell D1
D1: =MIN(IF(OFFSET(INDIRECT(C1),,1,,1)=C2,OFFSET(INDIRECT(C1),,,,1)))

4)End time of test depth is in cell D2
D2: =MIN(IF(OFFSET(INDIRECT(C1),,,,1)>D1,IF(OFFSET(INDIRECT(C1),,1,,1)<C2,OFFSET(INDIRECT(C1),,,,1))))

(Note: Commit those array formulas using [Ctrl]+[Shift]+[Enter]

To check dive_2, just type "dive_2" in Cell C1 and change the test depth in C2.

Does that give you what you're looking for?

Ron

[olasa]

I hope this is closer to what you want:

See encl. zip-file: http://www.excelforum.com/attachment...tid=3507&stc=1

Ola Sandström


Note:
I've have included a Defined NameRange called DataTable. If you use that instead of the Pivottable range (see the two pictures), Excel will Dynamically expand the Pivottable range = the Pivottable range will always be the length.

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>

[Bob Phillips]

The first time would be =MIN(IF(B2:B20=40,A2:A20)), the last would be
=MIN(IF(B2:B20=40,A2:A20))

Both are array formulae, and are committed with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

"akbreezo" <[email protected]> wrote in
message news:[email protected]...
>
> 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:
> 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=379185
>