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:
<tbody>
</tbody><colgroup><col span="8"><col><col><col><col><col span="2"></colgroup>
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>