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>

 

[cyrilbrd]

Hi,
Given in A1:

Year	Quarter	Ave
2003	1	123.5
2003	2
2003	3
2003	4
2004	1	160
2004	2
2004	3
2004	4
Date	Return
1/4/03	127
2/5/03	120
4/3/04	159
5/23/04	161

<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

Formula in C2 is =IFERROR(AVERAGE(IF(YEAR($A$13:$A$16)=A2,IF(ROUNDUP(MONTH($A$13:$A$16)/3,0)=B2,$B$13:$B$16))),"") Ctrl + Shift + Enter not just Enter on a PC or Command + Return on a MAC. Adjust range to fit your model or use Named ranges for greater range.
Would that work for you?

 

[Aladin Akyurek]

Quarter	Average Return for the Quarter
1/1/2003	123.5
4/1/2004	160
Date	Return
1/4/2003	127
2/5/2003	120
4/3/2004	159
5/23/2004	161

<COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4608" width=130><COL style="WIDTH: 163pt; mso-width-source: userset; mso-width-alt: 7736" width=218><TBODY>
</TBODY>

A6:B10 houses the data, the headers included.

B2, just enter and copy down:

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

This requires an Excel system of 2007 or later.

 

[cyrilbrd]

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

Question, shouldn't it exclude the first day of the next quarter? so 1st quarter is from 1st of Jan to 31st of March? with EDATE($A7,3)-1 no?

 

[Aladin Akyurek]

Question, shouldn't it exclude the first day of the next quarter? so 1st quarter is from 1st of Jan to 31st of March? either EDATE($A7,3)-1 or "<" no?

A2 = 1/1/203 (i.e., 1-Jan-2003)

B2:

=EMONTH(EDATE(A2,3),0)

would yield: 4/30/2003

So, >= A2 and <= EOMONTH(EDATE(A2,3),0)

would be correct.

While we are at it, we can eliminate EDATE for

=EOMONTH(A2,3)

will also yield: 4/30/2003

Given the latter, we can modify the task formula as:

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

 

[cyrilbrd]

What i meant is as follows:

First quarter: from the beginning of January to the end of March. Second quarter: from the beginning of April to the end of June.

so given that in A2 we have 1/1/203 then =EOMONTH(A2,3) would indeed return 4/30/2003 but we need 3/31/2003 that is =EOMONTH(A2,2) no?
So the formula would be if my understanding is correct =AVERAGEIFS($B$7:$B$10,$A$7:$A$10,">="&$A2,$A$7:$A$10,"<="&EOMONTH($A2,2))

see with :

Date	Return
1/4/03	127
2/5/03	120
4/1/03	159
5/23/04	161

<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

the results for the first quarter of 2003 should exclude 159.

 

[Aladin Akyurek]

I guess I need some coffee...

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

 

[cyrilbrd]

I guess I need some coffee...

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

Well caffeine has its perks...
I think your "<="&EOMONTH($A2,2) was better, if the date in A2 was 1/2/03 instead of 1/1/03, only EOMONTH($A2,2) would return the correct last day the quarter 3/31/03, while EDATE($A2,3)-1) would return 4/1/03...
hence unless mistaken =AVERAGEIFS($B$7:$B$10,$A$7:$A$10,">="&$A2,$A$7:$A$10,"<="&EOMONTH($A2,2)) would be the better formula.

 

[Aladin Akyurek]

Well caffeine has its perks...
I think your "<="&EOMONTH($A2,2) was better, if the date in A2 was 1/2/03 instead of 1/1/03, only EOMONTH($A2,2) would return the correct last day the quarter 3/31/03, while EDATE($A2,3)-1) would return 4/1/03...
hence unless mistaken =AVERAGEIFS($B$7:$B$10,$A$7:$A$10,">="&$A2,$A$7:$A$10,"<="&EOMONTH($A2,2)) would be the better formula.

QUARTER means 3 (my coffee notwithstanding), so it's more auditable to have EDATE(A2,3) in there. Whence my preference.

 

[kvsrinivasamurthy]

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)))