Hello, I've got the formula to identify that largest group of consecutive cells that fall below the threshold in a row of data and would like to show the column header of the first cell. Example data is below.
I'm using a CSE (array) formula: =MAX(FREQUENCY(IF(A3:N3<P3,COLUMN(A3:N3)),IF(A3:N3>P3,COLUMN(A3:N3)))) to identify the 'longest consecutive event' and would like cell S3 to show '9', which is the column header for the first cell in the longest consecutive event; and cell S4 to show '2', which is the first cell in the longest consecutive event on that row. Is this possible?
[IMG2=JSON]{"data-align":"none","data-size":"full","src":"https:\/\/http://www.ozgrid.com%5c/forum%5C/core%…=%22%7D%5B/IMG2] [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 896"]
[tr][td]A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][td]F
[/td][td]G
[/td][td]H
[/td][td]I
[/td][td]J
[/td][td]K
[/td][td]L
[/td][td]M
[/td][td]N
[/td][td]O
[/td][td]P
[/td][td]Q
[/td][td]R
[/td][td]S
[/td][/tr][tr]
[TD="align: right"] 1
[/TD]
[TD="align: right"] 2
[/TD]
[TD="align: right"] 3
[/TD]
[TD="align: right"] 4
[/TD]
[TD="align: right"] 5
[/TD]
[TD="align: right"] 6
[/TD]
[TD="align: right"] 7
[/TD]
[TD="align: right"] 8
[/TD]
[TD="align: right"] 9
[/TD]
[TD="align: right"] 10
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 12
[/TD]
[TD="align: right"] 13
[/TD]
[TD="align: right"] 14
[/TD]
90th Percentile
[/td][td]Threshold
[/td][td]Number of times data drops below threshold
[/td][td]Longest consecutive event
[/td][td]Start (Column Header)
[/td][/tr][tr]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 45
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 39
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 34
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 34
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 20
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 28
[/TD]
[TD="align: right"] 42
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 4
[/TD]
[TD="align: right"] 4
[/TD]
[TD="align: right"] 46
[/TD]
[TD="align: right"] 24
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 23
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 8
[/TD]
[TD="align: right"] 4
[/TD]
[/TABLE]