Jul 21 2021 02:39 PM
Jul 21 2021 02:39 PM
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.
Jul 21 2021 09:46 PM
@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.
Jul 22 2021 12:15 AM
@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.
Jul 22 2021 03:22 AM
@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.
Jul 22 2021 07:04 AM
@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?
Jul 22 2021 10:49 PM