Moving Weighted Average Between Timestamps

RyanAbood

New Member
Joined
Aug 2, 2017
Messages
2
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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

RyanAbood

New Member
Joined
Aug 2, 2017
Messages
2
Is it something I said? =(

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.
 
Joined
Feb 8, 2002
Messages
893
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,851
Messages
5,483,311
Members
407,393
Latest member
GeorgeBrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top