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

I re-did the post above with the correct columns and tried to update the description to make more sense