Time Intervals, Ranges, IF function, Lookup Function

CodenameAter

New member
Joined
Jul 23, 2014
Messages
4
Reaction score
0
Points
0
Hello All,

I was hoping to get some help with ranges, lookups and time intervals.

Table "Second Break Down" has every second listed (Col: N) and the individual (Col:O & P). The goal is to use the data from the "Time Adjusted Data" table to return a value of 1, 0 or -1 in the MBR column (Col: O) depending on which bowl that individual was in (Col: A). The way I want the formulas to work is as follows:

IF:
-Col:N is greater than or equal to any value in Col:J but less than the adjacent value (Col:K), but only if it has an adjacent value (therefore ignoring rows that values only in Col:J but not Col:K)
-Col:D is equal to the value in O9


Return:
"1" if Col:A has a value of "R"
"-1" if Col:A has a value of "W"
"0" if the value in Col:N does not fall within any of the time ranges in Col:J and Col:K


To explain this in longer terms:

If the time value falls within any of the time intervals in the Time Adjusted Data table and that time interval belongs to the individual in question, I want the respective value of 1, -1 or 0 to display beside the second in question. Therefore when I graph this, I get for each individual's location throughout the entire experiment.

For example, in Row 10, Col:J & K, we can see that MBR was on the "R" bowl (Col: A) from 00:00 to 2:42 seconds. Therefore in Col:O, I am hoping to see "1" repeating from 00:00 to 02:42. In row 15, the same thing except MLN was on the "W" bowl (Col: A) from 00:21 to 00:47, therefore in a Col:p, I would want to see "-1" from 00:21 to 00:47. Everything between Row 10 and Row 15 should be ignored since it doesn't have a value.

As I mentioned above, the goal is to plot all the data of each individual to see their location over the entire experiment, ie. I want to see where MBR was at different times, either R Bowl, W Bowl or nothing; comparing this to the rest of the data we have collected (not shown here to help keep things simple).


I hope that makes sense. Thank you.
 

Attachments

  • Foraging Time Data 22-July-2014 v2 (for forum).xlsx
    24.9 KB · Views: 17
Last edited by a moderator:
Is this what you mean
 

Attachments

  • Excel Guru - 3295 - Foraging Time Data.xlsx
    24.9 KB · Views: 22
Ya, Weazel came to the same conclusion my roommate did, use a SUMPRODUCT. But your comment good sir has lost me.
 
Is this the part where I tell you I tried to link the other post but your site won't let me till I reach "5 of something". I re-posted this after posting on Mr.Excel because I was under a time crunch today for my research and was also looking for differeing opinions. They were suggesting Index, Matches and I've been told there are a number of ways of doing this including SUMPRODUCTS, PIVOT TABLES and so forth.

On an unrelated note, I am often asked to review something that are being reviewed by other individuals simultaneously; this is quite a normal practice in my field of academia and the benefits of this should be obvious.

It is my understanding then that Bob replied with nothing on purpose. In my opinion this seems very unprofessional.

Anyway, I am sorry I offended you so greatly and would prefer to remove myself as clearly I am not made to feel welcome. I can promise you I will post here no more and deactivate my account immediately to avoid any further problems. Take care.
 
Good evening,

I am unrelated to this post, so my opinion may not be valid, but anyway... What Nos is getting at is that if you are going to involve different large groups of volunteer help that you should let each of them know about the other. I doubt Bob posted a blank answer on purpose. He's very professional (not to mention a genius). However, at least from my view, it appears he was working on an answer for you a 3 am when your question had already been answered... This is a good forum with lots of very intelligent people floating about, but if you feel as though you have been wronged I guess that's your business. The people here want to help you, they just feel as though you are abusing their time when multiple sites are involved without knowledge of one another...

Anyway, do with that what you will.

Have a good night,
 
For what it is worth now, the problem that was posted here seems to be significantly different to that posted at Mr Excel, I read it as needing some sort of double lookup. I couldn't see how to do that with formulae, so I created a VBA solution. I saved that workbook, as a .xlsm for the code, but then uploaded the original file instead of my amendment. I should have realised it as I change the filename to ExcelGuru - thread # - description, which is a lot different to what I uploaded - but I missed it. I put a fair bit of effort into this, if I had known he was cross-posting, I certainly wouldn't have bothered, and perhaps my upload error was serendipitous (for me :)).
 
Back
Top