Calculating time overlap

[T_Dawg]

Hello!

I need help with a formula to calculate overlapping observation time. We observe multiple subjects (3 or 4 at one time) but each subject comes in and out of view multiple times throughout the total observation time. I am can not figure out a formula to find out the total time that subject 1 and 2, 1 and 3, 1 and 4, 2 and 3...etc are both observed at the same time.

I have attached a sample of the times observed...

Thank you!

[bebo021999]

Can you calculate the desired results manually and put them where they should be in?

[T_Dawg]

Yes but I was hoping there was a formula that would save time...

[shg]

I think bebo is asking for an example of the results you'd like to see.

[shg]

maybe ...

Row\Col

A

B

C

D

E

F

G

H

I

J

K

L

M

2

Time Observable

3

PM

LU

MR

PM

LU

MR

4

00:00:00

00:09:45

00:00:00

00:10:00

00:00:00

00:09:45

00:00:00

x

x

x

5

00:11:40

00:36:50

00:11:05

00:40:22

00:11:40

00:36:50

00:09:45

x

6

00:45:32

00:50:31

01:36:12

01:36:41

00:45:32

00:50:31

00:10:00

7

00:53:20

00:57:08

00:53:20

00:57:08

00:11:05

x

8

00:59:35

01:01:24

00:59:35

01:01:24

00:11:40

x

x

x

9

01:04:00

01:20:56

01:04:00

01:20:56

00:36:50

x

10

01:29:44

01:25:40

01:29:44

01:25:40

00:40:22

11

01:34:10

01:36:57

01:34:10

01:36:57

00:45:32

x

x

12

01:40:36

01:42:30

01:40:36

01:42:30

00:50:31

13

00:53:20

x

x

14

00:57:08

15

00:59:35

x

x

16

01:01:24

17

01:04:00

x

x

18

01:20:56

19

01:25:40

20

01:29:44

21

01:34:10

x

x

22

01:36:12

x

x

x

23

01:36:41

x

x

24

01:36:57

25

01:40:36

x

x

26

01:42:30

27

#NUM!

28

#N/A

H4 and down: =IF(ISERROR(H3), NA(), SMALL($A$4:$F$12, COUNTIF($A$4:$F$12, "<=" & H3) + 1))

I4 and down: =IF(COUNTIFS(A$4:A$12, "<=" & $H4, B$4:B$12, ">" & $H4), "x", "")

Then copy I4:I28 to K4 and M4.

[shg]

BTW, I think you have some times reversed in row 10.

[T_Dawg]

Yes sorry I took out row 10 and reattached with the desired results!

[shg]

Row\Col

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

4

Time Observable

5

PM

LU

MR

Time

Duration

Who

6

00:00:00

00:09:45

00:00:00

00:10:00

00:00:00

00:09:45

00:00:00

00:09:45

7

PM

LU

00:35:24

7

00:11:40

00:36:50

00:11:05

00:40:22

00:11:40

00:36:50

00:09:45

00:00:15

2

PM

MR

01:11:12

8

00:45:32

00:50:31

01:36:12

01:36:41

00:45:32

00:50:31

00:10:00

00:01:05

0

LU

MR

00:35:24

9

00:53:20

00:57:08

00:53:20

00:57:08

00:11:05

00:00:35

2

10

00:59:35

01:01:24

00:59:35

01:01:24

00:11:40

00:25:10

7

11

01:04:00

01:20:56

01:04:00

01:20:56

00:36:50

00:03:32

2

12

01:25:40

01:29:44

01:25:40

01:29:44

00:40:22

00:05:10

0

13

01:34:10

01:36:57

01:34:10

01:36:57

00:45:32

00:04:59

5

14

01:40:36

01:42:30

01:40:36

01:42:30

00:50:31

00:02:49

0

15

00:53:20

00:03:48

5

16

00:57:08

00:02:27

0

17

00:59:35

00:01:49

5

18

01:01:24

00:02:36

0

19

01:04:00

00:16:56

5

20

01:20:56

00:04:44

0

21

01:25:40

00:04:04

5

22

01:29:44

00:04:26

0

23

01:34:10

00:02:02

5

24

01:36:12

00:00:29

7

25

01:36:41

00:00:16

5

26

01:36:57

00:03:39

0

27

01:40:36

00:01:54

5

28

01:42:30

00:00:00

0

29

#NUM!

00:00:00

0

30

#NUM!

00:00:00

0

I6 and down: =IF(ISERROR(I5), I5, SMALL($B$6:$G$14, COUNTIF($B$6:$G$14, "<=" & I5) + 1))

J6 and down: =IFERROR(I7-I6, 0)

K6 and down: = 4 * (COUNTIFS(B$6:B$14, "<=" & $I6, C$6:C$14, ">" & $I6) > 0) + 2 * (COUNTIFS(D$6:D$14, "<=" & $I6, E$6:E$14, ">" & $I6) > 0) + 1 * (COUNTIFS(F$6:F$14, "<=" & $I6, G$6:G$14, ">" & $I6) > 0)

P6: =SUM(SUMIF($K$6:$K$30, {6,7}, $J$6:$J$30))

P7: =SUM(SUMIF($K$6:$K$30, {5,7}, $J$6:$J$30))

P8: =SUM(SUMIF($K$6:$K$30, {3,7}, $J$6:$J$30))

[T_Dawg]

Thank you so much!!

I have a few more questions...

How would the formulas change if you had 4 subjects? (PM, LU, MR, and 1 more?)
and only 2 subjects?

[shg]

K6 and down: = 4 * (COUNTIFS(B$6:B$14, "<=" & $I6, C$6:C$14, ">" & $I6) > 0)
+ 2 * (COUNTIFS(D$6:D$14, "<=" & $I6, E$6:E$14, ">" & $I6) > 0)
+ 1 * (COUNTIFS(F$6:F$14, "<=" & $I6, G$6:G$14, ">" & $I6) > 0)

... would change to something like

= 8 * (COUNTIFS(...) > 0)
+ 4 * (COUNTIFS(...) > 0)
+ 2 * (COUNTIFS(...) > 0)
+ 1 * (COUNTIFS(...) > 0)

The values would range 0 to 15, and you'd adjust the formulas for columns I, K, and M accordingly.

[T_Dawg]

P6: =SUM(SUMIF($K$6:$K$30, {6,7}, $J$6:$J$30))
P7: =SUM(SUMIF($K$6:$K$30, {5,7}, $J$6:$J$30))
P8: =SUM(SUMIF($K$6:$K$30, {3,7}, $J$6:$J$30))


What would these formulas change to? I do not understand the numbers in {}....

Thank you!

[shg]

Think binary:

1 = 001 = MR only
2 = 010 = LU only
3 = 011 = LU & MR
4 = 100 = PM only
...

[T_Dawg]

I still dont understand...

[shg]

The formula in col K forms a number. Considering that number in binary, each bit represents whether a subject is in view. I don't think I can explain it more simply than that.

[T_Dawg]

ohhh I get it now! Thank you so much for your help. Will you marry me?

[shg]

You're welcome.