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:

1	2	3	4	5	6	7	8	9	90th Percentile	Threshold	Number of times data drops below threshold	Longest consecutive event	Start (Column Header)
34	50	34	25	20	25	28	42	50	50	30	4	4	?
50	50	25	25	25	50	50	25	50	50	30	4	3	?

<tbody>
</tbody><colgroup><col span="8"><col><col><col><col><col span="2"></colgroup>

 

[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.