# Need to calculate monthly averages from daily values

1. ## Need to calculate monthly averages from daily values

Please see attached spreadsheet. I would like to have a formula or macro that computes the values for column B in sheet 2 of my spreadsheet. These values should be monthly averages of the daily values listed in column B of sheet 1. As an example, cell B2 in sheet 2 should = average(sheet1!B2:B23)  Register To Reply

2. ## Re: Need to calculate monthly averages from daily values

A PivotTable would be the best way  Register To Reply

3. ## Re: Need to calculate monthly averages from daily values

This nice long array formula would work, too. Not quite as neat or efficient as a pivot table though.

=SUMPRODUCT(--(MONTH(Sheet1!\$A\$2:\$A\$1000)=MID(A2,FIND(".",A2),3)*100),--(YEAR(Sheet1!\$A\$2:\$A\$1000)=LEFT(A2,4)*1),Sheet1!\$B\$2:\$B\$1000)/COUNT(IF(MONTH(Sheet1!\$A\$2:\$A\$1000)=MID(A2,FIND(".",A2),3)*100,IF(YEAR(Sheet1!\$A\$2:\$A\$1000)=LEFT(A2,4)*1,Sheet1!\$A\$2:\$A\$1000)))  Register To Reply