Averaging values that move within a range

**hasanqz** · 06-11-2013, 06:25 PM

Hello,

I have a model that populates monthly price data based on a user input of start date and contract length. For example: If the user selects to run the model for 24 months starting July-2013, the price data for 24 months starting in July-2013 will show up in a range of cells corresponding to those months.

I want to set up a formula that calculates the average price for the first half of the input period. So if the user specifies the start date and contract length as above, the formula will average the first 12 months of price data starting in July 2013. Since both the start date and number of months changes based on user input, coming up with a formula is proving to be a bit difficult. I think the offset function would come into play here somehow but not exactly sure.

Thanks for your assistance.

**JBeaucaire** · 06-11-2013, 06:46 PM

Maybe the AVERAGEIFS() can be used to set the date range.

Assuming:

Dates in column A
Values in column B
Start date in D1
Number of months in E1

First half
=AVERAGEIFS(B:B, A:A, ">="&D1, A:A, "<"&Date(Year(D1), Month(D1)+(E1/2), Day(D1)))

Second half
=AVERAGEIFS(B:B,A:A, ">="&DATE(YEAR(D1), MONTH(D1)+(E1/2), DAY(D1)),A:A, "<"&DATE(YEAR(D1), MONTH(D1)+E1, DAY(D1)))