Excel formula to return column header from first cell from consecutive cells below a threshold

[zara007]

I've got the formula to identify that largest group of consecutive cells that fall below a 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: =<code>MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>K3,COLUMN(A3:I3))))</code>
to identify the 'longest consecutive event' and would like cell N3 to show '4', which is the column header for the first cell in the longest consecutive event. Is this possible?

A | B | C | D | E | F | G | H | I | J | K | L | M | N | ...[column]
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | X | Threshold | y*| z*| |...[header]
20| 52| 61| 23| 18| 25| 25| 40| 42| X | 30 | 5 | 4 | ? |...[data]

y* -> Number of times data drops below threshold, identified with formula:
<code>=FREQUENCY(A3:I3,K3)
</code> z* -> Longest consecutive event, identified with CSE (array) formula:
<code>=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>K3,COLUMN(A3:I3))))
</code>? -> need formula to bring back column header of the first cell in the longest consecutive cells.


data:

[TABLE="width: 705"]
<tbody>[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]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"]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] ?[/TD]
[/TR]
[TR]
[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"]4[/TD]
[TD="align: right"]3[/TD]
[TD] ?[/TD]
[/TR]
</tbody><colgroup><col span="8"><col><col><col><col><col span="2"></colgroup>[/TABLE]

 

[Joe4]

Duplicate: https://www.mrexcel.com/forum/excel...entified-consecutive-cells-meet-criteria.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.