 # monthly average each year how to calculate monthly average per year

3 Replies

# Welcome to your Excel discussion space!

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.

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.

# Re: monthly average each year

Simplest way is to create PivotTable aggregating on average.

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

# Re: monthly average each year

This solves the issue: 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.