# 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)

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

A PivotTable would be the best way

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)))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1