Thread: Moving Weighted Average Between Timestamps Thanks: 0 Likes: 0

1. Moving Weighted Average Between Timestamps

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.

2. Re: Moving Weighted Average Between Timestamps

Is it something I said? =(

Originally Posted by RyanAbood
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.

3. Re: Moving Weighted Average Between Timestamps

To restate this, you have a series of entries in Column A and then time stamps in column B.

You want to average the last ten time stamps.

If I could tell you that the last time stamp is in cell B500. Then, would you agree with the following logic: If I compare the time in B490 to the time in B500. The delta between those is the time to do 10 units. Thus, the average of all the gaps (B490-B491, B491-B492, ... B499-B500) is exactly the same as =(B500-B490)/10. Please... feel free to try this 20 times with your real data to prove that both calculations are exactly the same.

To find the row number with the last entry in column B, use =MATCH(999999,B:B,1).
To find the row that is 10 rows above that, use =MATCH(999999,B:B,1)-10
But, to make sure that last row never goes negative, use =MAX(1,MATCH(999999,B:B,1)-10)

To find the time in the last row with data
=INDEX(B:B,MATCH(999999,B:B,1))
To find the time ten rows before that
=INDEX(B:B,MAX(1,MATCH(999999,B:B,1)-10))
To find the total time elapsed for 10 units
=INDEX(B:B,MATCH(999999,B:B,1))-INDEX(B:B,MAX(1,MATCH(999999,B:B,1)-10))
To find the average per unit:
=(INDEX(B:B,MATCH(999999,B:B,1))-INDEX(B:B,MAX(1,MATCH(999999,B:B,1)-10)))/10

The one problem: this average will be wrong until you have 10 units produced.

Bill