Solved

# Rolling average by site

Posted on 2014-02-12
209 Views
For each unique SiteID listed in column B of the attached Excel sheet, I would like to generate 7-day rolling averages of the data found in columns E, F and G.

I have manually generated the 7-day rolling averages for the first two sites (04CEF-1 and 04COK-1) in columns H, I, and J. The next set of 7-day averages should begin in row 189 (as indicated in the attached spreadsheet).

I am hoping for a solution that will automate the process of generating the 7-day rolling averages by SiteID.

Most of the SiteIDs have 92 days of data, however, this is not always the case so the solution should be able to accommodate varying data counts for each SiteID.

Many thanks in advance for any solutions!
rollingavg.xlsx
0
Question by:dougf1r
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 39854765
Try using this formula in H5 copied down and across

=IF(COUNTIF(\$B2:\$B8,\$B2)=7,AVERAGE(E2:E8),"")

see attached

regards, barry
rollingavg-barry.xlsx
0

LVL 1

Author Comment

ID: 39854919
Works great. Many thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.