Interval detection formula.

[Maher]

Hi all,
I have a worksheet with 2 columns : one containing values
of a timer start and one for values of timer stop.
These values represent time intervals :

Worksheet 1
START STOP
4 16 <- interval of time [4,16]
8 18
21 29
28 32
29 33

On another worksheet I constructed a column (time axis)
with values starting from 0 to the greatest value of timer
stop (with a given step).
For each value (Ai) of my second worksheet, I would like
to compute the number of rows in the first worksheet
having the interval containing Ai.

Worksheet 2
A B
0 ? (should be 0)
5 ? (should be 1, because only one interval contains
5 :[4,16])
10 ? (should be 2, because two interval contain 10 :
[4,16] and [8,18])
15 ? (should be 2)
20 ? (should be 0)
25 ? (should be 1)
30 ? (should be 2)
35 ? (should be 0)

Any idea about the formula to do this ?

Thank you,
Maher

 

[Dave Peterson]

I put the value to return in A1 and this formula in B1:

=SUMPRODUCT(--(A1>=Sheet1!$A$1:$A$5),--(A1<=Sheet1!$B$1:$B$5))

And it seemed to work ok.

Hi all,
I have a worksheet with 2 columns : one containing values
of a timer start and one for values of timer stop.
These values represent time intervals :

Worksheet 1
START STOP
4 16 <- interval of time [4,16]
8 18
21 29
28 32
29 33

On another worksheet I constructed a column (time axis)
with values starting from 0 to the greatest value of timer
stop (with a given step).
For each value (Ai) of my second worksheet, I would like
to compute the number of rows in the first worksheet
having the interval containing Ai.

Worksheet 2
A B
0 ? (should be 0)
5 ? (should be 1, because only one interval contains
5 :[4,16])
10 ? (should be 2, because two interval contain 10 :
[4,16] and [8,18])
15 ? (should be 2)
20 ? (should be 0)
25 ? (should be 1)
30 ? (should be 2)
35 ? (should be 0)

Any idea about the formula to do this ?

Thank you,
Maher