averageifs of value if 2 time range overlap

[fitzxlr8s]

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

A2:A5 size unit
B2:B5 time range beginning
C2:C5 time range end

$d$2 : Date1
$d$3 : Date2

I'm trying to find the average size of units that were active during D2:D3

I can identify if time ranges intersect with:

=if(or( c2:c5 <d2,d3<b2:b5), "overlap", "no overlap")<d2.d3<b2:b5), "overlap",="" "no="" overlap")<d2,d3<b2:b5),="" no="" overlap")


But I don't know how to use this to find the average of the units.

=if(or(and(b2:b5>=d2, b2:b5<=d3),and(d2>=b2:b5,d2<=c2:c5),average(a2:a5),0)

I know this is wrong. Any help would be appreciated</d2.d3<b2:b5),>

 

[fitzxlr8s]

for some reason my first formula isn't showing up in my post.

=if(or(and(C2:c5>=D2,D3< B2:B5), "overlap", "no overlap")

 

[Peter_SSs]

Welcome to the MrExcel board!

If you have a formula with a "<" symbol followed by a letter, the forum software thinks it it is HTML code and the result is what happened to you. If that happens, just edit your post and put a space between those two characters, as I have done for you in your second post.

Now, in relation to your question, could we see some sample data and expected result(s) with an explanation related directly to that sample?

 

[fitzxlr8s]

Thank you Peter. Here's a quick sample:

[TABLE="width: 669"]
<colgroup><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c [/TD]
[TD][/TD]
[TD][/TD]
[TD]d[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Size[/TD]
[TD]contract_start[/TD]
[TD]contract_End[/TD]
[TD][/TD]
[TD][/TD]
[TD]Intervals[/TD]
[TD]Avg_Size_ Active[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]400[/TD]
[TD]1/1/2014[/TD]
[TD]4/1/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/1/2014[/TD]
[TD]625.00[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500[/TD]
[TD]2/1/2014[/TD]
[TD]5/2/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]4/1/2014[/TD]
[TD]740.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]400[/TD]
[TD]3/1/2014[/TD]
[TD]8/1/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]7/1/2014[/TD]
[TD]1033.33[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1200[/TD]
[TD]4/1/2014[/TD]
[TD]7/1/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]10/1/2014[/TD]
[TD]1500.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1500[/TD]
[TD]5/1/2014[/TD]
[TD]11/1/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/1/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]100[/TD]
[TD]6/1/2014[/TD]
[TD]6/2/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]4/1/2015[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 605"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody></tbody>[/TABLE]


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.

 

[Peter_SSs]

Thank you Peter. Here's a quick sample:

Could you ..
a) confirm whether your dates are d/mm/yy or m/dd/yy?
b) explain how you manually calculated those results?

 

[fitzxlr8s]

Could you ..
a) confirm whether your dates are d/mm/yy or m/dd/yy?
b) explain how you manually calculated those results?

a) my dates are mm/dd/yy
b) Here's how I manually calculated the "E" column

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

 

[Peter_SSs]

Try this. To avoid the error values you might want to wrap the formula in an IFERROR() function.

Excel Workbook
	A	B	C	D	E
1	Size	contract_start	contract_End	Intervals	Avg_Size_ Active
2	400	1/01/14	1/04/14	1/01/14	625
3	500	1/02/14	2/05/14	1/04/14	683.3333333
4	400	1/03/14	1/08/14	1/07/14	1033.333333
5	1200	1/04/14	1/07/14	1/10/14	1500
6	1500	1/05/14	1/11/14	1/01/15	#DIV/0!
7	100	1/06/14	2/06/14	1/04/15	#DIV/0!
Average