Jul 05 2021 11:09 PM
guys please help,
how to calculate monthly average per year
thanks for your help
Jul 05 2021 11:26 PM
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.
Jul 06 2021 12:16 AM
Simplest way is to create PivotTable aggregating on average.
Create a PivotTable to analyze worksheet data - Office Support (microsoft.com)
Jul 06 2021 12:31 AM - edited Jul 06 2021 12:37 AM
This solves the issue:
How it works:
=IFERROR(AVERAGEIFS($C$39:$C$58,$B$39:$B$58,">="&$E39,$B$39:$B$58,"<="&EOMONTH($E39,0)),0)
N.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.