45-Calendar Day Rolling Average, Max, and Min

[MJBalk]

I have created a simple "dashboard" type tool for inexperienced Excel users in our plant to enter data and provide a reporting sheet to their teams, but I am very rusty in Excel and need some help.

My data is set up to enter a new row of data with a date and points in columns A,B, then Column 3 calculates the % based on a static "Possible Points" field on another sheet.

[TABLE="width: 536"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]TOTAL POINTS[/TD]
[TD]% SCORE[/TD]
[TD]Goal[/TD]
[/TR]
[TR]
[TD]1/17/18[/TD]
[TD]62[/TD]
[TD]82%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/18/18[/TD]
[TD]64[/TD]
[TD]84%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/22/18[/TD]
[TD]64[/TD]
[TD]84%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/23/18[/TD]
[TD]65[/TD]
[TD]86%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/24/18[/TD]
[TD]68[/TD]
[TD]89%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/25/18[/TD]
[TD]67[/TD]
[TD]88%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/29/18[/TD]
[TD]62[/TD]
[TD]82%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/30/18[/TD]
[TD]64[/TD]
[TD]84%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]1/31/18[/TD]
[TD]64[/TD]
[TD]84%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]2/1/18[/TD]
[TD]65[/TD]
[TD]86%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]2/5/18[/TD]
[TD]68[/TD]
[TD]89%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]2/6/18[/TD]
[TD]67[/TD]
[TD]88%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]2/7/18[/TD]
[TD]68[/TD]
[TD]89%[/TD]
[TD]85%[/TD]
[/TR]
[TR]
[TD]2/8/18[/TD]
[TD]68[/TD]
[TD]89%[/TD]
[TD]85%[/TD]
[/TR]
</tbody>[/TABLE]

On another tab, I have a printable dashboard. I need to calculate and automatically update as new data is added the MIN and MAX over the last 45 calendar days, the current week's average % score and last week's average % score in a set of specific, static cells on another tab.

I'm feeling like an absolute novice these days having not used Excel really at all in well over a year. I tried several formulas I found in the threads but nothing's working. Can you help?

MJ

 

[MJBalk]

anyone?