Hello All,
I will try my best to describe what I am trying to achieve. Attached is a spreadsheet for reference:
TimeStudy.xlsx
Essentially, I am attempting to capture a time study. Here are the details:
1) There are different possible steps in a procedure, labeled a through h. This procedure only goes up to e.
2) There are a different number of trials of the same procedure.
3) The steps of the procedure are recorded using a stopwatch. The number that is input for each step is the time that the stop watch displays at the end of the step. For example, I begin the procedure and time t = 0 and step a finishes at 7 seconds so I enter "7" into B3.
4) The stopwatch may run continuously. Following the example from before, a ends at 7 seconds and b happens to end at 15 seconds. Therefore, 15 is recorded in B4. However, after step "e", the procedure repeats itself and therefore you can have a scenario such as the one in the spreadsheet where a, the beginning step, is greater than the previous e because the clock runs continuously.
5) On top of that, you can reset the stopwatch and start from 0, as you will see in trials 3 and 4. This is obvious because D3<C7, etc.
I am attempting to develop a spreadsheet which automatically calculates the average trial time and the total step time based on the differences between step times. As you will see, I have figured out the formula to calculate the trial times and therefore the average trial time. And you will also see that, for the most part, I have found the formula for calculating the average step times.
However, I am struggling to develop a formula for step a due to the time rolling over from trial 1 to 2 (for example) since the stopwatch was not reset. I know I can use the following formula: = (B3+(C3-B7)+D3+E3+(F3-E7))/5 but this does not adapt well to new data and is only set up for consistent trials of start, continue, stop, stop, continue. The other formulas for b through e will work regardless of the data entered.
Hopefully this is not too confusing and sorry for the huge wall of text but I figured the more detail the better.
Thanks in advance - truly!
P.S. - you'll see in my step time formula that I divide by 5, the number of trials. Is there a way to identify the number of columns that have data and divide by that? Instead of having to change the formula dependent on # of trials.
Bookmarks