# Thread: Time Intervals, Ranges, IF function, Lookup Function

1. ## Time Intervals, Ranges, IF function, Lookup Function

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.

2. Is this what you mean

3. Originally Posted by Bob Phillips
Is this what you mean
Nothing showed up on the excel sheet. I ended up using a SUMPRODUCT, very straight forward, I was just way over thinking the problem

4. Nothing showed up on the excel sheet.
Maybe Bob just knew when to invest time in your problem and when not to......

http://www.mrexcel.com/forum/excel-q...two-dates.html

5. Ya, Weazel came to the same conclusion my roommate did, use a SUMPRODUCT. But your comment good sir has lost me.

6. But your comment good sir has lost me
You should read this http://www.excelguru.ca/node/7 it's also linked to in the rules over at MrExcel which you apparently overlooked.

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

8. Good evening,

Anyway, do with that what you will.

Have a good night,

9. 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 ).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•