Calculate and compare rolling averages with daily data entry?

I have a sheet set up to track measurements, which are entered daily in E20:E384.

I have set up a formula to calculate how today's measurement compares to measurements of 2 weeks ago (Cell AI2) and 4 weeks ago (Cell AI3).

However, one data point vs. another data point makes for volatile comparisons, so I want to set up a rolling average which subtracts the 3-day-rolling-average of 2 weeks ago from the most recent 3-day-rolling-average. Also want to do the same for the vs. 4 weeks ago comparison. (Note: as for which 3 days, I would say the actual 2 weeks ago data point, then the data points before and after).

I'm stuck though--can anybody suggest a formula? I suspect this would use OFFSET.

Hi,

I am a beginner with Excel and I am looking to find an example as to how to build a single rolling average plot/chart that has a separate daily input score below that could be anywhere from +10 to -10 for example. I'd like the information on two plots that is fed from a table - but it could be all on one chart/plot, but only if it is not too cluttered.

This blog topic seems to be what I am looking for. I sure hope that I can see an example, or have someone point me in a different direction with an URL.

(My first post on this forum)

Thank you,

Karl

Sorry for the delay, all--work and that. The sheet this came from is large and very elaborate, so I created a cell-for-cell copy, eliminating unnecessary data/formatting.

Highlighted in yellow are the relevant bits. You can see the formulas I used to create an ongoing "last data point vs 2 weeks ago and vs 4 weeks ago".

Again, I want to write a formula that gives me a 3-day average for more stable numbers.

E.g., instead of calculating the change between November 19th and December 3rd, calculate the change between the average of November 18th, 19th and 20th, and the average of December 1st, 2nd and 3rd.

2-4WkRolAvgCompar.xlsx

Hi,

Just getting back to the forum today. Sorry to have side-stepped the rules here. I'll post a new thread.

Thanks !

Karl

FDibbins--

Sorry once again for the delay and the bump, but I wanted to thank you as your solution highlighted above DOES work well. Also wanted to let any future readers of the thread know that this will give you what you're looking for RE stable rolling averages.

Cheers!