Rolling Average of multiple cities

[Walterk1]

I have looked at MrExcel, OzGrid, and ExcelForums in addition to Googling terms for rolling average. I have seen examples of formulas, and in some cases using Pivot Tables too. I looked at DSUM/D functions, but I was not sure if it would work well. I have not see a good mirror of our issue.

Need: We need to be able to calculate a 12 month rolling Avg based on a selected date. I need to get Avg for multiple sites, based on chosen date.

Summary:
We take data that is downloaded from Lotus Notes DB. The Columns and Rows have been condensed from total dataset. The data appears to be sorted by Site and then Date. The data will continue to grow with time - right now it is 480+ rows, but will grow with time.

Column A - Date, B - Site, C - Hours Worked, D - Accidents.

Some months, we may not have a Site listed. That, along with the order being by site (not date), made me think that I could not rely on X number of rows in my formulas.

I included only 2 sites and only 14 months of data. I calculated the Accident average for the 2 sites by months selected. I will create a date selection input, so when they choose - the averages calculate automatically based on the previous 12 months. (my 1/1/2010 entry means Jan 2009 - Dec 2009 and 2/1/2010 means Feb 2009 - Jan 2010)

I welcome any advice folks may have. I am building this for users I cannot rely to sort by Date/Site (to arrange data). I plan to make a summary sheet that will populate the Running Average based on the month/year they select.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Site	Total Work Hours	Accidents	*	*	*	*
2	01/01/09	Dallas	775,067	71	*	1/1/2010	*	*
3	01/01/09	Atlanta	5,998	0	*	*	Accidents 12 M Avg	Work Hours
4	02/01/09	Dallas	5,182	0	*	Dallas	9.25	95,731.75
5	02/01/09	Atlanta	67,834	0	*	Atlanta	0.75	20,685.83
6	03/01/09	Dallas	109,831	24	*	*	*	*
7	03/01/09	Atlanta	80,352	5	*	3/1/2010	*	*
8	04/01/09	Dallas	127,608	1	*	*	*	*
9	04/01/09	Atlanta	9,952	0	*	Dallas	3.42	31,998.33
10	05/01/09	Dallas	101,995	12	*	Atlanta	0.83	21,033.25
11	05/01/09	Atlanta	46,399	1	*	*	*	*
12	06/01/09	Dallas	1,818	0	*	*	*	*
13	06/01/09	Atlanta	6,687	0	*	*	*	*
14	07/01/09	Dallas	4,813	0	*	*	*	*
15	07/01/09	Atlanta	2,753	1	*	*	*	*
16	08/01/09	Dallas	2,736	0	*	*	*	*
17	08/01/09	Atlanta	4,002	0	*	*	*	*
18	09/01/09	Dallas	10,721	3	*	*	*	*
19	09/01/09	Atlanta	11,704	0	*	*	*	*
20	10/01/09	Dallas	1,409	0	*	*	*	*
21	10/01/09	Atlanta	5,774	0	*	*	*	*
22	11/01/09	Dallas	4,952	0	*	*	*	*
23	11/01/09	Atlanta	2,773	2	*	*	*	*
24	12/01/09	Dallas	2,649	0	*	*	*	*
25	12/01/09	Atlanta	4,002	0	*	*	*	*
26	01/01/10	Dallas	10,266	1	*	*	*	*
27	01/01/10	Atlanta	10,167	1	*	*	*	*
28	02/01/10	Dallas	1,266	0	*	*	*	*
29	02/01/10	Atlanta	6,051	1	*	*	*	*
Sheet1

 

[AlphaFrog]

If you have Excel 2007, you could use the AVERAGEIFS function

In cell G4, put this formula to get the 12 month (dates in column A) Average from the date 1/1/2010 (F2), for Dallas (F4) of the data in column D

=AVERAGEIFS(D:D, A:A, "<="&F2, A:A, ">"&Date(Year(F2)-1, Month(F2), Day(F2)), B:B, F4)

 

[Walterk1]

Rats - I should have noted that I/we have Excel 2003.
I will look at the function and see if there is a way to make a backwards compatible hack.

 

[AlphaFrog]

SUMPRODUCT can SUM based on multiple criteria and COUNT based on multiple criteria. Maybe try something like this in G4

=SUMPRODUCT((A2:A29< =F2)*(A2:A29>DATE(YEAR(F2)-1,MONTH(F2),DAY(F2)))*(B2:B29=F4)*(D2:D29))/
SUMPRODUCT((A2:A29< =F2)*(A2:A29>DATE(YEAR(F2)-1,MONTH(F2),DAY(F2)))*(B2:B29=F4))

 

[AlphaFrog]

Another way this could be done is to use Autofilter to set up your criterias and then use the SUTOTAL function to get the Average of the filtered data

Excel AutoFilter Basics
Use the SUBTOTAL Function with AutoFiltered Lists
SUBTOTAL function

 

[Walterk1]

cool - I will try the Sumproduct route and report back. I was not sure about the Count piece of the puzzle.
Autofilter would not really work - the data is used for other calcs in the file (DSUM) based on criteria. Maybe that might still work, but I want to keep the users from manipulating Autofilter and subtotals.
The file is basically a dump and report from Notes. They will do various charting and summarization of data, and I do not think I could get it all and train them to manage the file - cleanly.

 

[Walterk1]

SOLVED Re: Rolling Average of multiple cities

I just wanted to say thanks to AlphaFrog - I was able to transfer the sumproduct example to my data set, along with a dynamic named range (to compensate for increasing data).
This nailed it on the head! I had read about sumproduct previously, but did not "see the light".
Thanks again.