Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF in multiple columns based on other criteria in Excel? | Excel Discussion (Misc queries) | |||
counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
conditional formula - based on 2 separate criteria | Excel Worksheet Functions | |||
Is it possible to use the sumif function based on the criteria of. | Excel Worksheet Functions | |||
returning a value based on mulitple criteria | Excel Worksheet Functions |