guys please help, how to calculate monthly average per year thanks for your help

osamashokeir 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. Please include the following info to help others answer your question:Device and OS platform, e.g., PC/Windows 10 (see https://support.microsoft.com/en-us/windows/which-version-of-windows-operating-system-am-i-running-628bec99-476a-2c13-5296-9dd081cdd808) or Mac/macOS Big Sur 11.2.1 (see https://support.apple.com/en-us/HT201260)Excel product name and version number (see https://support.microsoft.com/en-us/office/about-office-what-version-of-office-am-i-using-932788b8-a3ce-44bf-bb09-e334518b8b19)Excel file you’re working with or sample file with data – please remove all confidential and PII data As much context of what you're trying to do - and why - as possible, e.g., how what you're trying to do fits into what larger process or workflow*Expected result and method to achieve the result** 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: How it works: Write first Date of each month for respected years in Column E & apply cell format mmm-yyyy.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.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.

monthly average each year | Microsoft Community Hub

3 Replies

Rajesh_Sinha
Iron Contributor
Jul 06, 2021
osamashokeir
This solves the issue:

How it works:
Write first Date of each month for respected years in Column E & apply cell format mmm-yyyy.
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.
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.
SergeiBaklan
Diamond Contributor
Jul 06, 2021
osamashokeir

Simplest way is to create PivotTable aggregating on average.

Create a PivotTable to analyze worksheet data - Office Support (microsoft.com)
Lorenzo
Silver Contributor
Jul 06, 2021
osamashokeir
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.

Please include the following info to help others answer your question:
Device and OS platform, e.g., PC/Windows 10 (see https://support.microsoft.com/en-us/windows/which-version-of-windows-operating-system-am-i-running-628bec99-476a-2c13-5296-9dd081cdd808) or Mac/macOS Big Sur 11.2.1 (see https://support.apple.com/en-us/HT201260)
Excel product name and version number (see https://support.microsoft.com/en-us/office/about-office-what-version-of-office-am-i-using-932788b8-a3ce-44bf-bb09-e334518b8b19)
Excel file you’re working with or sample file with data – please remove all confidential and PII data
As much context of what you're trying to do - and why - as possible, e.g., how what you're trying to do fits into what larger process or workflow*
Expected result and method to achieve the result**

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.

Forum Discussion

monthly average each year

3 Replies

Welcome to your Excel discussion space!

Resources