Remember Me?

#1
June 14th 05, 11:46 PM
 akbreezo Posts: n/a
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

#2
June 15th 05, 12:19 AM
 olasa Posts: n/a

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
http://www.datapigtechnologies.com/ExcelMain.htm-

+-------------------------------------------------------------------+
|Filename: Book4.zip |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760

#3
June 15th 05, 12:25 AM
 akbreezo Posts: n/a

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

#4
June 15th 05, 12:34 AM
 olasa Posts: n/a

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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760

#5
June 15th 05, 12:47 AM
 Bob Phillips Posts: n/a

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

#6
June 15th 05, 12:56 AM
 akbreezo Posts: n/a

The pivot table looks promising, but I am having trouble getting it to
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

#7
June 15th 05, 02:31 AM
 Ron Coderre Posts: n/a

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

#8
June 15th 05, 10:53 PM
 olasa Posts: n/a

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

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Scott Powell Excel Discussion (Misc queries) 9 April 13th 05 02:32 PM Tricia S. Excel Worksheet Functions 10 March 17th 05 02:17 PM andrewo-s Excel Worksheet Functions 10 March 14th 05 03:35 AM Shelba Excel Worksheet Functions 1 February 2nd 05 05:40 AM Brad Excel Worksheet Functions 6 December 31st 04 08:14 AM

All times are GMT +1. The time now is 10:20 PM.