I'm working on a pretty cool, albeit challenging (to me) problem, and I've gotten pretty far thanks to the good people of this forum and I need some help to make a good solution GREAT!
Production Output Tracking:
Cycle times are around 20-60 seconds for each cell, and there are 20 cells, so we are producing at least 60 and in some cases 200 data points per hour per cell, so there is a lot of data!
Data Recording: I programmed a USB input device, in this case, a big red button, wired to each production cell and when the operator completes a unit, they hit the button; each cell has their own button that corresponds to their cell number. Excel records the output in column A and with the COUNTIF function, I can easily tabulate and display how many each cell has done and the cells can race each other for bragging rights. That part is done.
Next I was able to time stamp each input in column A with a corresponding timestamp in column B. That too is working.
What I would love to show them next to their =COUNTIF cell total, is a weighted average time per unit based on their cell's time stamps. This would allow line leads to know exactly who is running off the pace and give additional training or additional headcount to a lagging cell and to be able to see quickly whether the additional help solved the problem.
Plain Written Code:
So to illustrate the goal I'll walk you, (and myself) through the problem in plain words for Cell 1.
Every time Cell 1 makes a unit, the number 1 is deposited in Row A. At the same time, Row B records a time stamp in HH:MM:SS.
The cycle time in cell 1 is 30s, so they produce 2 units a minute, which is also 120 units per hour. The time between units would be 30 seconds.
A "Fast cell" would run the same item at 20s, and a "slow cell" would be 40 seconds.
The perfect metric would be the average time between their last 10 units, constantly updating as they continue to build units AKA enter 1 in Row A. I'm pretty close to calculating their total overall average time, but it's not fluid enough to give good data like the average of JUST the last 10 units out of a fairly large pile of data...all while there are 19 other cells creating data too.
I've been playing with CountIf(Offset) that I found here: Excel A "running average" that averages only the last 8 entries?
But I'm just not getting where I need to go.
I'm totally open to even redesigning my sheet from scratch, save for the fact that I need one sheet to continuously capture the "1+ENTER" of cell 1, the "2+ENTER" of cell 2 and the "3+ENTER" of cell 3 as the cells all complete units.
Production Output Tracking:
Cycle times are around 20-60 seconds for each cell, and there are 20 cells, so we are producing at least 60 and in some cases 200 data points per hour per cell, so there is a lot of data!
Data Recording: I programmed a USB input device, in this case, a big red button, wired to each production cell and when the operator completes a unit, they hit the button; each cell has their own button that corresponds to their cell number. Excel records the output in column A and with the COUNTIF function, I can easily tabulate and display how many each cell has done and the cells can race each other for bragging rights. That part is done.
Next I was able to time stamp each input in column A with a corresponding timestamp in column B. That too is working.
What I would love to show them next to their =COUNTIF cell total, is a weighted average time per unit based on their cell's time stamps. This would allow line leads to know exactly who is running off the pace and give additional training or additional headcount to a lagging cell and to be able to see quickly whether the additional help solved the problem.
Plain Written Code:
So to illustrate the goal I'll walk you, (and myself) through the problem in plain words for Cell 1.
Every time Cell 1 makes a unit, the number 1 is deposited in Row A. At the same time, Row B records a time stamp in HH:MM:SS.
The cycle time in cell 1 is 30s, so they produce 2 units a minute, which is also 120 units per hour. The time between units would be 30 seconds.
A "Fast cell" would run the same item at 20s, and a "slow cell" would be 40 seconds.
The perfect metric would be the average time between their last 10 units, constantly updating as they continue to build units AKA enter 1 in Row A. I'm pretty close to calculating their total overall average time, but it's not fluid enough to give good data like the average of JUST the last 10 units out of a fairly large pile of data...all while there are 19 other cells creating data too.
I've been playing with CountIf(Offset) that I found here: Excel A "running average" that averages only the last 8 entries?
But I'm just not getting where I need to go.
I'm totally open to even redesigning my sheet from scratch, save for the fact that I need one sheet to continuously capture the "1+ENTER" of cell 1, the "2+ENTER" of cell 2 and the "3+ENTER" of cell 3 as the cells all complete units.
