# AverageIF quarter dates

#### joeshu26

##### Board Regular
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>

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### cyrilbrd

##### Well-known Member
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?

##### MrExcel MVP
 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

##### Well-known Member
=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?

Last edited:

##### MrExcel MVP
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

##### Well-known Member
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.

##### MrExcel MVP
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

##### Well-known Member
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.

##### MrExcel MVP
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

##### Well-known Member
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;
Code:
``  =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)))``

Replies
4
Views
82
Replies
3
Views
100
Replies
1
Views
171
Replies
9
Views
154
Replies
7
Views
473

1,196,007
Messages
6,012,831
Members
441,732
Latest member
Ayon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back