AverageIF quarter dates

J

joeshu26

Board Regular
Joined
Oct 30, 2013
Messages
136
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]
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Hi,
Given in A1:
[TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]Year[/TD]
[TD="class: xl64, width: 65"] Quarter
[/TD]
[TD="class: xl64, width: 65"]Ave[/TD]
[/TR]
[TR]
[TD="align: right"]2003[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]123.5[/TD]
[/TR]
[TR]
[TD="align: right"]2003[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: right"]2003[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: right"]2003[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: right"]2004[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]160[/TD]
[/TR]
[TR]
[TD="align: right"]2004[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: right"]2004[/TD]
[TD="class: xl64, align: right"]3[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: right"]2004[/TD]
[TD="class: xl64, align: right"]4[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl64"] Date
[/TD]
[TD="class: xl64"]Return[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/4/03[/TD]
[TD="class: xl64, align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2/5/03[/TD]
[TD="class: xl64, align: right"]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4/3/04[/TD]
[TD="class: xl64, align: right"]159[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5/23/04[/TD]
[TD="class: xl64, align: right"]161 [/TD]
[TD][/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

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?
 
Upvote 0
[TABLE="width: 260"]
<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>[TR]
[TD="class: xl63, width: 130, bgcolor: white"]Quarter[/TD]
[TD="class: xl63, width: 218, bgcolor: white"]Average Return for the Quarter[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 130, bgcolor: white"]1/1/2003[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]123.5[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 130, bgcolor: white"]4/1/2004[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]160[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 130, bgcolor: white"]Date[/TD]
[TD="class: xl63, width: 218, bgcolor: white"]Return[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 130, bgcolor: white"]1/4/2003[/TD]
[TD="class: xl63, width: 218, bgcolor: white"]127[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 130, bgcolor: white"]2/5/2003[/TD]
[TD="class: xl63, width: 218, bgcolor: white"]120[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 130, bgcolor: white"]4/3/2004[/TD]
[TD="class: xl63, width: 218, bgcolor: white"]159[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 130, bgcolor: white"]5/23/2004[/TD]
[TD="class: xl63, width: 218, bgcolor: white"]161[/TD]
[/TR]
</TBODY>[/TABLE]

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.
 
Upvote 0
Aladin Akyurek said:
=AVERAGEIFS($B$7:$B$10,$A$7:$A$10,">="&$A2,$A$7:$A$10,"<="&EOMONTH(EDATE($A2,3),0))
Click to expand...
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:
Upvote 0
cyrilbrd said:
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?
Click to expand...

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))
 
Upvote 0
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.
Click to expand...

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 :
[TABLE="width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 65, bgcolor: transparent"] Date
[/TD]
[TD="class: xl64, width: 65"]Return[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1/4/03[/TD]
[TD="class: xl64, align: right"]127[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2/5/03[/TD]
[TD="class: xl64, align: right"]120[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4/1/03[/TD]
[TD="class: xl64, align: right"]159[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5/23/04[/TD]
[TD="class: xl64, align: right"]161 [/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]



the results for the first quarter of 2003 should exclude 159.
 
Upvote 0
Aladin Akyurek said:
I guess I need some coffee...

=AVERAGEIFS($B$7:$B$10,$A$7:$A$10,">="&$A2,$A$7:$A$10,"<="&EDATE($A2,3)-1)
Click to expand...
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.


 
Upvote 0
cyrilbrd said:
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.
Click to expand...

QUARTER means 3 (my coffee notwithstanding), so it's more auditable to have EDATE(A2,3) in there. Whence my preference.
 
Upvote 0
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)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Basic IF & IF/AND Not Working…
Replies
2
Views
236
zero269
Z
S
  • Solved
converting a date to "Quarter 3 2023/24"
Replies
5
Views
247
Joe4
Joe4
C
  • Locked
  • Question Question
Changing conditional formatting to fit quarters / months rather than days
Replies
1
Views
115
Fluff
Fluff
JenniferMurphy
  • Question Question
Help with coin-collecting sheet
Replies
3
Views
137
felixstraube
F
M
  • Question Question
COUNTIFS formula for number of dates in a quarter??
Replies
4
Views
964
Alex Blakenburg
A

Forum statistics

Threads
1,222,405
Messages
6,165,864
Members
451,988
Latest member
boo203

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top