Moving Time Frame

[kubapilc]

Hello All,

I have a little problem and I cannot find the answer for it.
Mainly, I need to find average value for each calendar year between 2 contract dates.
Imagine you have a contract:
Start date 01/04/2011
End date 31/03/2014

You have raw market value data for each day from 01/01/2011 till 05/05/2014.
So your spreadsheet looks like:
Date , Market Value
01/01/2011 45.65
02/01/2011 56.65
.....
05/05/2014 35.65

The task is to find an average for each year between contract start date and end date
For instance, the final answer would like:
01/04/2011-31/03/2012 = average 56.56
01/04/2012-31/03/2013 = average 45.56
01/04/2013 -31/03/2014 = average 35.65

is it possible to have a formula which calculate averages ?
I would like to change the contract start date and end date so the averages will be calculated accordingly for each calendar year between contract dates.

Thank you for all your help in advance

Regards
Jake

 

[Somendra Misra]

Hi Jake,

Welcome to Chandoo.org Forum.

I doubt on your result:

Try below formula in F4 and copy down.

=IF(ROWS(F$4:F4)<=YEAR($F$2)-YEAR($F$1),AVERAGEIFS($B$2:$B$804,$A$2:$A$804,">="&EDATE($F$1,(ROWS(F$4:F4)*12)-12),$A$2:$A$804,"<="&EDATE($F$1,ROWS(F$4:F4)*12)-1),"")

Regards,

 

[Luke M]

Should be something like:
=AVERAGEIFS(B2:B100,A2:A100,">="&StartDate,A2:A100,"<="&EndDate)

Fill in cell references as appropriate.

 

[Luke M]

Ah, I mis-read the question. Looks like Somendra's formula will work though.