Return column header of first cell identified in consecutive cells that meet criteria

[zara007]

Hello, I would like to be able to get the column header of the first cell from an identified event of consecutive cells that fall below a threshold. Example data is below (column headers on 2nd row).

I can identify the longest consecutive event, when more than one cell has data that falls below the threshold, using the following CSE formula: =MAX(FREQUENCY(IF(A3:N3
P3,COLUMN(A3:N3))))

In column S, I'd like the cell to return '9' for the first row of data (the first cell at the start of the longest event) and '1' for the next row.

[TABLE="width: 900"]
<tbody>[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]
[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"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]50
[/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][/TD]
[/TR]
[TR]
[TD="align: right"]22
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]50
[/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]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help would be greatly appreciated.</P3,COLUMN(A3:n3)),if(a3:n3>

 

[zara007]

Links to cross posted sites:
https://www.mrexcel.com/forum/excel-questions/1057479-excel-formula-return-column-header-first-cell-consecutive-cells-below-threshold.html#post5077811

https://www.excelguru.ca/forums/showthread.php?9046-Return-column-header-from-first-cell-from-consecutive-cells-below-a-threshold

https://www.ozgrid.com/forum/forum/help-forums/excel-formulas/1203930-return-column-header-from-first-cell-from-consecutive-cells-below-a-threshold

https://www.excelguru.ca/forums/showthread.php?9046-Return-column-header-from-first-cell-from-consecutive-cells-below-a-threshold&p=37149&posted=1#post37149

https://www.mrexcel.com/forum/excel-questions/1057446-return-column-header-first-cell-identified-consecutive-cells-meet-criteria.html

https://superuser.com/questions/1327146/excel-formula-to-return-column-header-from-first-cell-from-consecutive-cells-bel?noredirect=1#comment1980266_1327146

http://www.msofficeforums.com/newreply.php?do=newreply&noquote=1&p=129061