0

Objective: to find the amount of time it takes to switch from one activity to the next. In order to say the switch is complete 5 activities in a row for the same operation must be under 7 minutes each.

Attempted Solution: I used a Match(Frequency( matrix formula to find the location where the activity normalizes. I've posted it below:

{=MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0)}

What Info is referenced: Column I is a helper column reporting if the time for the activity is within time parameters, and posts a 1 if it is. Column B is the list of activities. and cell J6 is the activity the user wants to analyze. (for the formula on the bottom, column E is the dates/times for the activities)

Issue with Formula: The formula works fine until there are more than 5 activities within the timeframe in a row that are correct, in which case the formula keeps counting until it find a negative, then reports the last successful one. I need the formula to stop at 5 successful matches.

If it helps anyone with a similar problem the entire formula is posted below though there aren't any problems with the rest other than its length.

=INDEX(E1:E20000,MAX(IF(OFFSET(B2,0,0,MAX(IF(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500 )),IF(I2:I500=1,0,ROW(B2:B500))))>4,0) - 1)=J6,ROW(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0) - 1)))) - 1)=J6,ROW(OFFSET(B2,0,0,MAX(IF(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500= 1,0,ROW(B2:B500))))>4,0) - 1)=J6,ROW(OFFSET(B2,0,0,MATCH(TRUE,(FREQUENCY(IF((I2:I500=1)*(B2:B500=J6),ROW(B2:B500)),IF(I2:I500=1,0,ROW(B2:B500))))>4,0) - 1)))) - 1)))))

Resources used:

How to count unique values in Excel with two conditions

Extracting the top 5 maximum values in excel

Spreadsheet snap:

enter image description here

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
LWTK
  • 1
  • 5
  • 2
    It would help if you would mock up a minimal example in tabular form and expected output for that minimal data set. sometimes it is easier for us to understand your question that way. we may even be able to suggest better approaches. – Scott Craner May 10 '18 at 18:57
  • 1
    1. That is not minimal, recreate your needs in a small table, only including what is needed to get the job done. See [MCVE] 2 use [edit] to put any further explanation in the original post. – Scott Craner May 10 '18 at 19:10
  • Better? sorry I'm somewhat new to posting on stack overflow. Been using it as a reference for forever, but never posted much. – LWTK May 10 '18 at 19:53

1 Answers1

0

I found a solution to my issue by working in the helper column and making the helper post a 1 if the parameters are met and a 0 if they are not. The formula for the helper column is below

=IF(F3<TIME(0,7,0),IF(ISERROR(MATCH(TRUE,LEFT(B$1:B2,1)=LEFT(B3,1),0)),1,IF(COUNTIF(G$1:G2,1)>0,1,0)),0)

The final formula takes into account the new format of the helper column, and reports the first position of the first 5 consecutive values by finding the location of the end of the first set of 5 using a match(true,Frequency( formula, then a bottom up match to find the first wrong position. I added a 1 to this number and used an index to post the corresponding date column.

=INDEX(D:D,MAX(IF(OFFSET(G1,0,0,MATCH(TRUE,(FREQUENCY(IF((G2:G2000=1),ROW(B2:B2000)),IF(G2:G2000=1,0,ROW(B2:B2000))))>4,0)-5)=0,ROW(OFFSET(G1,0,0,MATCH(TRUE,(FREQUENCY(IF((G2:G2000=1),ROW(B2:B2000)),IF(G2:G2000=1,0,ROW(B2:B2000))))>4,0)-5))-ROW(INDEX(OFFSET(G1,0,0,MATCH(TRUE,(FREQUENCY(IF((G2:G2000=1),ROW(B2:B2000)),IF(G2:G2000=1,0,ROW(B2:B2000))))>4,0)-5),1,1))+1))+1)
LWTK
  • 1
  • 5