Need help looking up value based on criteria
 

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

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-


+-------------------------------------------------------------------+
|Filename: Book4.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3502 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=379185

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


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

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


+-------------------------------------------------------------------+
|Filename: Book4.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3503 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=379185

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" wrote in
message ...

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

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!


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

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(INDIR ECT(C1),,,,1)))

4)End time of test depth is in cell D2
D2:
=MIN(IF(OFFSET(INDIRECT(C1),,,,1)D1,IF(OFFSET(IND IRECT(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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=379185

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.


+-------------------------------------------------------------------+
|Filename: Book4.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3507 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=379185