monthly average each year

Copper Contributor

osamashokeir_1-1625551475594.png

guys please help,

 

how to calculate monthly average per year 

thanks for your help

3 Replies

@osamashokeir 

Welcome to your Excel discussion space!

 

If you’re looking to ask a question or start a conversation about Excel, you’re in the right place!  Ask away.

 

Please include the following info to help others answer your question:

 

Once you got a good answer to your question, please accept it as solution to help others looking for answer to the same/similar question.

@osamashokeir 

Simplest way is to create PivotTable aggregating on average. 

Create a PivotTable to analyze worksheet data - Office Support (microsoft.com)

@osamashokeir 

This solves the issue:

 

RajeshS_0-1625555601508.png

 

How it works: 

  1. Write first Date of each month for respected years in Column E & apply cell format mmm-yyyy.
  2. Enter this formula in cell F39 & fill down.

 

 

=IFERROR(AVERAGEIFS($C$39:$C$58,$B$39:$B$58,">="&$E39,$B$39:$B$58,"<="&EOMONTH($E39,0)),0)

 

 

N.B.

  1. Formula returns ZERO for moths, are not available in Column B.

 

You may use this formula also:

 

 

=IFERROR(SUMPRODUCT((MONTH($B$39:$B$58)=MONTH(E39)*(YEAR($B$39:$B$58)=YEAR(E39)))*($C$39:$C$58))/(SUMPRODUCT((MONTH($B$39:$B$58)=MONTH(E39)*(YEAR($B$39:$B$58)=YEAR(E39)))*1)),0)

 

 

 

Accept this as Best response as well Like, if my post solves the issue.