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

excelquestions1234

New Member
Joined
Aug 28, 2015
Messages
3
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:

8/1/20158/2/20158/3/20158/4/20158/5/20158/6/20158/7/20158/8/2015
People196184561398288268249203
Cost$35,170$31,451$54,573$50,740$45,846$45,037$41,599$36,590
Orders Cost$179.44$170.93$97.28$127.49$159.19$168.05$167.06$180.25
Average Revenue$414$438$859$688$572$549$538$460

<tbody>
</tbody>

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

TOPLINE8/8/2015
People203
Cost$36,590
Orders Cost$180.25
Average Revenue$460

<tbody>
</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)?


TOPLINE8/27/2015Trailing 7-Day Avg.Previous 2 DoW
People203????
????​
Cost$36,590????
????​
Orders Cost180.25
Average Revenue$460

<tbody>
</tbody>
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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())
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top