Average if time ranges overlap

Originally Posted by Debraj Roy

I am lil bit confused.. answer is also not matching with you..

still can you please check this one..

In P8 use formula as

Formula:

=AVERAGEIFS($H$8:$H$13,$I$8:$I$13,"<="&$M8,$J$8:$J$13,">="&$M8)

you can drag for other dates..

Here's how i manually calculated the E values

E1: units A1, A2, A3, A4 all have time ranges that overlap D1:D2

400 + 500 +400 +1200 = 2500
2500 / 4 units = 625

E2: units A1, A2, A3, A4, A5, A6 all overlap D2:D3
4100 / 6 units = 683 (mistake in my original data. can Correct in edit, sorry)

E3: Units A3, A4, A5 overlap D3:D4
3100 / 3 units = 1033

E4: Units A5 is the only unit to overlap D4:D5
1500 / 1 unit = 1500

E5: Not units overlap D5:D6