Need help figuring out how to use AVERAGEIFS on a range of values modified by another cell

[bradleyp2005]

I have a data set with home listings, and I'm trying to answer the question, "For homes in inventory at the end of a given month, how many days have they been on the market on average?"

Data specifics: Column A:Location Column W:List Date Column X: Close Date Column

I need to average the days a home has been in inventory at the end of a given month. I.e...home is listed on 1/1/2016, and closed on 2/15/2016. I need this row to use 31 in the average calculation. The idea would be like this (which isn't a viable formula, but hopefully it makes sense):


=AVERAGEIFS(EOMONTH(C$28,0)-'MLS Data'!$W:$W'MLS Data'!$A:$A),Branch_Filter,'MLS Data'!$W:$W,"<="&EOMONTH(C$28,0),'MLS Data'!$X:$X,">"&EOMONTH(C$28,0))

where C$28 is the beginning of the month in my summary table.

This is the last component to a month-long project and my brain is fried, so hopefully someone out there smarter than I can help me out!

 

[oldbrewer]

I need to average the days a home has been in inventory at the end of a given month. I.e...home is listed on 1/1/2016, and closed on 2/15/2016. I need this row to use 31 in the average calculation. The idea would be like this (which isn't a viable formula, but hopefully it makes sense):
			01/01/2016	01/02/2016	01/03/2016	01/04/2016	01/05/2016	01/06/2016	01/07/2016	01/08/2016	01/09/2016	01/10/2016	01/11/2016	01/12/2016	01/01/2017	01/02/2017
			31/01/2016	29/02/2016	31/03/2016	30/04/2016	31/05/2016	30/06/2016	31/07/2016	31/08/2016	30/09/2016	31/10/2016	30/11/2016	31/12/2016	31/01/2017	28/02/2017
home	listed	closed
home1	01/01/2016	15/02/2016	31	15	0	0	0	0	0	0	0	0	0	0	0	0
home2	25/01/2016	12/03/2016	7	29	12	0	0	0	0	0	0	0	0	0	0	0
home3	27/02/2016	20/04/2016	0	3	31	20	0	0	0	0	0	0	0	0	0	0
			col D
		formula in D3 (the 31 in home1 row)
	=IF($C6<D$3,0,IF(AND($B6<D$3,$C6>D$4),D$4-D$3+1,IF(AND($B6<=D$3,$C6>=D$4),D4-D3+1,IF(AND($B6<=D$3,$C6<D$4),$C6-D$3+1,IF(AND($B6>D$3,$B6<=D$4,$C6>D$4),D$4-$B6+1,IF(AND($B6>D$3,$C6<=D$4),$C6-$B6+1,0))))))
		you can now determine average times for each house on the market
		and how many houses were on the market in a given month

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[bradleyp2005]

The formula in D3 in your example could just be D$2-$B2;

But what I'm going for is a formula that avoids the 12 columns needed for the Days calculation presented here.

I'm guessing it's just not possible.

 

[bradleyp2005]

Formula in D3 would need* to be D$2-B$2 to capture the total days in inventory for that home through the end of a given month.

 

[oldbrewer]

but if as in the case of home 1 the time period is spread over several months, it depends what you want - eg do you just want time on books for a given month end ?