Create a dynamic rolling trailing 7-day average that auto updates

[excelquestions1234]

I am creating a dashboard and I want it to automatically create a 7-day trailing average based off the the =today() cell that it looks up on.

So I have the raw data in a sheet that has the data by day:

People

Cost

Orders Cost

Average Revenue

<tbody>
[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]8/2/2015[/TD]
[TD="align: right"]8/3/2015[/TD]
[TD="align: right"]8/4/2015[/TD]
[TD="align: right"]8/5/2015[/TD]
[TD="align: right"]8/6/2015[/TD]
[TD="align: right"]8/7/2015[/TD]
[TD="align: right"]8/8/2015[/TD]

[TD="align: right"]196[/TD]
[TD="align: right"]184[/TD]
[TD="align: right"]561[/TD]
[TD="align: right"]398[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]268[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]203[/TD]

[TD="align: right"]$35,170[/TD]
[TD="align: right"]$31,451[/TD]
[TD="align: right"]$54,573[/TD]
[TD="align: right"]$50,740[/TD]
[TD="align: right"]$45,846[/TD]
[TD="align: right"]$45,037[/TD]
[TD="align: right"]$41,599[/TD]
[TD="align: right"]$36,590[/TD]

[TD="align: right"]$179.44[/TD]
[TD="align: right"]$170.93[/TD]
[TD="align: right"]$97.28[/TD]
[TD="align: right"]$127.49[/TD]
[TD="align: right"]$159.19[/TD]
[TD="align: right"]$168.05[/TD]
[TD="align: right"]$167.06[/TD]
[TD="align: right"]$180.25[/TD]

[TD="align: right"]$414[/TD]
[TD="align: right"]$438[/TD]
[TD="align: right"]$859[/TD]
[TD="align: right"]$688[/TD]
[TD="align: right"]$572[/TD]
[TD="align: right"]$549[/TD]
[TD="align: right"]$538[/TD]
[TD="align: right"]$460[/TD]

</tbody>

I have a lookup in another sheet that dynamically can update the cell:

TOPLINE

People

Cost

Orders Cost

Average Revenue

<tbody>
[TD="align: right"]8/8/2015[/TD]

[TD="align: center"]203[/TD]

[TD="align: right"]$36,590[/TD]

[TD="align: right"]$180.25[/TD]

[TD="align: right"]$460[/TD]

</tbody>

The formula looks like this: =vlookup(A3,'Raw Data'!$A$1:$AF$40,match($D$1,'Raw Data'!$B$1:$JL$1,0),False)

How do I do this same thing for the trailing 7-day average as well as the previous 2 same DoW (-7days -14 days)?

TOPLINE	Trailing 7-Day Avg.	Previous 2 DoW
People
Cost
Orders Cost
Average Revenue

<tbody>
[TD="align: right"]8/27/2015[/TD]

[TD="align: center"]203[/TD]
[TD="align: right"]????[/TD]
[TD="align: right"]

????​

[/TD]

[TD="align: right"]$36,590[/TD]
[TD="align: right"]????[/TD]
[TD="align: right"]

????​

[/TD]

[TD="align: right"]180.25[/TD]

[TD="align: right"]$460[/TD]

</tbody>

 

[bstory84]

I think i may have something for you.
You should be able to use the AverageIfs function.
So I made some assumptions.... Sorry if the references are incorrect. but i duplicated what was on your post and i tested. So here you go.
My assumptions are that..
1. Dates are in ROW 1 of worksheet Raw Data.
2. Value titles are in Column A of worksheet Raw Data(People, Cost, ...)

=AVERAGEIFS(INDIRECT("'Raw Data'!"&MATCH($A2,'Raw Data'!$A$1:$A$5,0)&":"&MATCH($A2,'Raw Data'!$A$1:$A$5,0)),'Raw Data'!$1:$1,">="&TODAY()-6,'Raw Data'!$1:$1,"<="&TODAY())

 

[excelquestions1234]

I think i may have something for you.
You should be able to use the AverageIfs function.
So I made some assumptions.... Sorry if the references are incorrect. but i duplicated what was on your post and i tested. So here you go.
My assumptions are that..
1. Dates are in ROW 1 of worksheet Raw Data.
2. Value titles are in Column A of worksheet Raw Data(People, Cost, ...)

=AVERAGEIFS(INDIRECT("'Raw Data'!"&MATCH($A2,'Raw Data'!$A$1:$A$5,0)&":"&MATCH($A2,'Raw Data'!$A$1:$A$5,0)),'Raw Data'!$1:$1,">="&TODAY()-6,'Raw Data'!$1:$1,"<="&TODAY())

All the references you made are correct but It does not seem to be working for me. Any idea why?

 

[bstory84]

The Category names would have to match exactly. So "People" would not match to "people" . So make sure those are the same. Also make sure your dates are actually numbers formatted as dates, and are not strings "text" If you would like please reply back telling me how the data is stores. IE.. what columns and or rows and worksheet names the data exists in.

 

[excelquestions1234]

The Category names would have to match exactly. So "People" would not match to "people" . So make sure those are the same. Also make sure your dates are actually numbers formatted as dates, and are not strings "text" If you would like please reply back telling me how the data is stores. IE.. what columns and or rows and worksheet names the data exists in.

Thanks for your help! I got it to work. Really appreciate it.

For the past 2 same day of week would it look like this:
=AVERAGEIFS(INDIRECT("'Raw Data'!"&MATCH($A3,'Raw Data'!$A$1:$A$46,0)&":"&MATCH($A3,'Raw Data'!$A$1:$A$46,0)),'Raw Data'!$1:$1,"="&TODAY()-7,'Raw Data'!$1:$1,"="&TODAY()-14)