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:
<tbody>
</tbody>
I have a lookup in another sheet that dynamically can update the cell:
<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)?
<tbody>
</tbody>
So I have the raw data in a sheet that has the data by day:
8/1/2015 | 8/2/2015 | 8/3/2015 | 8/4/2015 | 8/5/2015 | 8/6/2015 | 8/7/2015 | 8/8/2015 | |
People | 196 | 184 | 561 | 398 | 288 | 268 | 249 | 203 |
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:
TOPLINE | 8/8/2015 |
People | 203 |
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)?
TOPLINE | 8/27/2015 | Trailing 7-Day Avg. | Previous 2 DoW |
People | 203 | ???? | ???? |
Cost | $36,590 | ???? | ???? |
Orders Cost | 180.25 | ||
Average Revenue | $460 |
<tbody>
</tbody>