AverageIF quarter dates

[joeshu26]

Hello,

I am trying to average a list of numbers in the second table based on which quarter and year they fall into. 1st quarter of 2003 corresponds in the table below to 1/01/2003, 2nd quarter of 2004 is 4/04/2014, etc. In my second column I am trying to get an average of the second column of the second table below based on if the month is 1 2 or 3 for 1st quarter, 4 5 or 6 for second quarter, etc. and if the year matches the year in the Quarter column. Any Ideas?

Quarter	Average Return for the Quarter
1/01/2003
4/01/2004

<TBODY>
</TBODY>

Date	Return
1/4/2003	127
2/5/2003	120
4/3/2004	159
5/23/2004	161

<TBODY>
</TBODY>

 

[Aladin Akyurek]

Quarter Average Return for the Quarter
01-01-2003 123.5
01-04-2004 160



Date Return
04-01-2003 127
05-02-2003 120
03-04-2004 159
23-05-2004 161


Formula in B2;

  =SUMPRODUCT((YEAR($A$8:$A$11)=YEAR($A2))*(INT(MONTH($A$8:$A$11)/4)=INT(MONTH($A2)/4)),$B$8:$B$11)/SUMPRODUCT((YEAR($A$8:$A$11)=YEAR($A2))*(INT(MONTH($A$8:$A$11)/4)=INT(MONTH($A2)/4)))

Really? Why would one advise such while:

=AVERAGEIFS($B$7:$B$10,$A$7:$A$10,">="&$A2,$A$7:$A$10,"<="&EDATE($A2,3)-1)

is adequate and efficient?