Selected Answer
Hi Sunil,
You are right. It's a little complicated. And it will take a lot of work. I want you to shoulder a larger part of that work. Let's do this together.
In essence, [LINK URL="' https://www.teachexcel.com/talk/5475/worksheet-event-for-sheetcalculate"]the solution is here[/LINK]. And this solution is based on a similar one I made earlier. I want you to study these solutions and then prepare your own project so that they can be implemented. It won't be a copy & paste matter. Instead the method will be adapted to your needs. I will do that eventually.
What's missing in your workbook?
You say that the current production volume updates every second. You want an average of those updated numbers. Your workbook has no place to collect the numbers. Therefore no average can be drawn. And your question doesn't define how that place should be managed. I imagine you will collect readings every second for 1, 3 and 5 hours, then draw an average when the time is up, delete the collected data and start afresh. Maybe so, maybe differently but apparently all 5-fold, for 5 production lines. Actually, this is a thread in itself and the solution is in the provided links.
Once the averages are collected you want them recorded and evaluated. The place to record them is in the table "Averge Production Data" which you have provided and the evaluation will be in Sheet1. The latter appears all done. The data on Sheet1 will change when the average production data change. I'm not sure I fully understand but now is not the time to correct any misunderstandings. Before you have collected the averages there is no need to design exact formulas for processing them.
What you do need to understand at this time is how to manage the calculations. That will be done by an automatic loop built by the same method as demonstrated in the linked threads, with a timer. You mention intervals of 1, 3 and 5 hours. That isn't clear. A timer can be set to run every hour, i.e. an interval of 1 hour, or "after" 1, 3 and 5 hours, or after 1 hour and then at an interval of 2 hours. This needs to be clarified but I don't think your workbook needs any adjustment. Anyway, that isn't the fist thing that will be done.
So, the plan I suggest is to do the average collection first, then the evaluation of the averages and, finally, create a timer system to run the evaluations automatically at the specified times. That is 3 threads (3 questions), each one with its own information, discussion and solution. Your part of the job is to neatly divide the tasks and provide the information for each in a timely manner. For that you need to understand the intended solution which you will after studying the linked workbooks.
Just bear in mind that you can't just ask 3 questions and expect 3 answers. The sequence is ask, implement, test and ask the next question building on the advance enabled by the previous answer. You will not be able to ask the second question intelligently before you have received and understood the answer to the first.
Your current question is both too large and too imprecise. I suggest that we should be satisfied to have made the plan here.
