Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 14th 05, 11:46 PM
akbreezo
 
Posts: n/a
Default 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


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


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

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


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

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


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

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

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





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


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

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


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

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


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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF in multiple columns based on other criteria in Excel? Scott Powell Excel Discussion (Misc queries) 9 April 13th 05 02:32 PM
counting cells (COUNTIF) based on two or more criteria Tricia S. Excel Worksheet Functions 10 March 17th 05 02:17 PM
conditional formula - based on 2 separate criteria andrewo-s Excel Worksheet Functions 10 March 14th 05 03:35 AM
Is it possible to use the sumif function based on the criteria of. Shelba Excel Worksheet Functions 1 February 2nd 05 05:40 AM
returning a value based on mulitple criteria Brad Excel Worksheet Functions 6 December 31st 04 08:14 AM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017