Weekly and Monthty moving avarage

Occasional Contributor

Hi Team, 

I have searched and found you. As I couldn't find it on the web.


I am trying to find a formula that shows the avarage over the past week and month with every new day that I enter. A formula that moves with every new entry I make without having to do this manualy every time.


Your help will be greatly appreciated even if there is no solution to this.


7 Replies

@Marty1990 I'm afraid your screenshot isn't revealing very much. What values are you trying to average? And since you want averages by week, month etc., where are the dates that would allow you to do such calculations? Better to upload a schedule that gives a total picture of what you want to achieve. Replace any private or confidential information with fake data.

@Riny_van_Eekelen Thanks for getting back to me! And sorry for some unclear information.


In the new attached schedule you can see the collected date of every weekday. Collum E F G show the data collected from each worker on 3 periods of that day. (morning, midday, evening). Collum I J K is the avarege  of that day on the 3 points of measuring. 


All the way on the right you can see the all time avarege and the other avarege points I want to create. 

Is there a formula that moves with every new entry I make that shows the average of the past 5 days, 30 days, etc. Without having to change this manualy every day.

Taking this data from eather the E F G colum or the I J K colum wouldn't matter as the will be the same calculating the average. 


Hope this makes more sense now! And thanks again. 


@Marty1990 Perhaps totally clear in your mind but not in mine. Again, you uploaded a screenshot. All I can see from that is that the manner you collect the data is not optimal. If you upload the worksheet, I or someone else, can perhaps come up with a better way to structure the data and do the calculation you desire.

@Riny_van_Eekelen Righto, Attached I have the file. Hopefully this explains it better. 

@Marty1990 It's a start, but I still don understand what you want. The only formulae you have in there are some with a SUM function. Even on row 5 (col N to P in the DOWN sheet), where you suggest that these are "All time averages", they are just sums.


Then there are several weeks in the DOWN sheet, Which week should be averaged in, for instance, cells N7:P7?


In other words, you need to be more specific in what needs to go where. Can you just calculate some of the numbers (without a formula), enter them in the sheet in the correct place and explain the logic. 


And where/how does the UP sheet come into play?

Your data set is missing Dates ,,, and be specific for Weekly, Monthly average what would be criteria like which week's/month's and from which DATE !!