Average if time ranges overlap

I'm at a loss. I've searched high and low for a solution.

HTML Code: 

I'm try to find out which units were active during the intervals set in d (d2:d3, d3:d4, etc) and then average out the size of those units in e.

I can identify which ones are active with a variation of the following:

=if(or(and(C1:c6>=D1, D2< B1:B6), "overlap", "no overlap")

But I don't know how to use this to find the average of the units that match the criteria. I was something of something like this:

=if(or(and(b1:b6>=d2, b1:b6<=d2),and(d1>=b1:b6,d1<=c1:c6),average(a1:a6),0)

I know this is wrong. Any help would be appreciated. I have other conditions (5-6, but none with "and" or "or") to meet asides from this time range overlap criteria, but I think I can figure out how to implement these once I get pass this hurdle. Anything helps at this point.

As you can see... its hard to determine the stracture..

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

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:

Please Login or Register  to view this content.

you can drag for other dates..

Maybe ...

Row\Col	A	B	C	D	E	F
1	Size	start	end	Intervals	Avg Active
2	400	01/01/2014	04/01/2014	01/01/2014	625.0	E2: =IFERROR(AVERAGEIFS($A$2:$A$7, $B$2:$B$7, "<=" & D3, $C$2:$C$7, ">=" & D2), 0)
3	500	02/01/2014	05/02/2014	04/01/2014	683.3
4	400	03/01/2014	08/01/2014	07/01/2014	1033.3
5	1200	04/01/2014	07/01/2014	10/01/2014	1500.0
6	1500	05/01/2014	11/01/2014	01/01/2015	0.0
7	100	06/01/2014	06/02/2014	04/01/2015

You're welcome.