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?

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Quarter[/TD]
[TD]Average Return for the Quarter[/TD]
[/TR]
[TR]
[TD]1/01/2003[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/01/2004[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="width: 500"]
<TBODY>[TR]
[TD]Date[/TD]
[TD]Return[/TD]
[/TR]
[TR]
[TD]1/4/2003[/TD]
[TD]127[/TD]
[/TR]
[TR]
[TD]2/5/2003[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]4/3/2004[/TD]
[TD]159[/TD]
[/TR]
[TR]
[TD]5/23/2004[/TD]
[TD]161[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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?