# Get column header of first cell of longest consecutive set of cells below a given threshold

I have a formula that calculates the length of the longest group of consecutive cells in a row of data that fall below a certain threshold.

I would like to show the column header of the first cell of that group. So in the example below I would like cell `N3` to display `4`, which is the value of the column header cell `D1`. Is this possible?

Example Data:

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

y* -> The number of times the data drops below the threshold, calculated with the formula:

``````=FREQUENCY(A3:I3,K3)
``````

z* -> The length of the longest consecutive set of cells below the threshold, calculated with the CSE (array) formula:

``````{=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>K3,COLUMN(A3:I3))))}
``````

h* -> Required formula to return the column header of the first cell of the longest consecutive set of cells.

• So the answer should be `M`? – DavidPostill May 30 '18 at 11:59
• The answer should be '4', which is the header for the first cell in the longest series of consecutive cells below the 'threshold' (which is '30' in the example above). – zara007 May 30 '18 at 12:41
• Column 'M' is a calculation of the longest series of consecutive cells below the threshold (30): in the example, these start at column D (23, 18, 25, 25). – zara007 May 30 '18 at 12:44

Your y* formula, `=FREQUENCY(A3:I3,K3)`, actually calculates the number of times the data drops below, or is equal to, the threshold value. To only count values below the threshold, and assuming the data consists only of integer values, you need to use this formula: `=FREQUENCY(A3:I3,K3-1)`.

The `FREQUENCY` part of your z* formula, `FREQUENCY(IF(A3:I3<K3, COLUMN(A3:I3)), IF(A3:I3>K3, COLUMN(A3:I3)))`, should, strictly speaking, have a `>=` instead of the `>`. In your full z* formula, where you just extract the maximum of the counts, it actually makes no difference. However, it may not work correctly when used in a more complex formula. For example, my solution formula doesn't work correctly with the `>` (for the edge case where the value immediately before the longest sequence equals the threshold value).

The corrected z* formula is:

``````{=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))))}
``````

Using this corrected formula as a base leads to the following solution (array-entered into `N3`), which extracts the column header of the first cell of the longest sequence:

``````{=INDEX(1:1,IFERROR(SMALL(IF(A3:I3>=K3,COLUMN(A3:I3)),MOD(MAX(10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))+ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1),10^5))+1,COLUMN(A3:I3)))}
``````

Explanation:

The prettified version of the above formula is as follows:

``````{=
INDEX(
(1:1),
IFERROR(
SMALL(
IF(A3:I3>=K3,COLUMN(A3:I3)),
MOD(
MAX(
10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))
+ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1+IF(1,,"N:N needs to match the column of the cell this formula is entered into")
),
10^5
)
)+1,
COLUMN(A3:I3)
)
)}
``````

The way the formula works is that it modifies the `FREQUENCY()` "bin" counts so they also contain the bin index. Then the index is extracted from the bin count corresponding to the longest sequence and used with `SMALL()` to obtain the lower threshold for that bin. This threshold is the column number of the cell just before the first cell of the longest sequence. Finally, the column number of the first cell is used with `INDEX()` to obtain the first cell's header.

• `FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))``{1;0;4;0;0}`, the bin counts array
• `10^5*{1;0;4;0;0}``{100000;0;400000;0;0}`, the scaled bin counts array
• `COUNT(IF(A3:I3>=K3,))``4` which is one less than the number of bins (this counts the interval threshold values, but the number of bins is one more than that)
• Thus `ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1``ROW(INDEX(N:N,1):INDEX(N:N,5))-1``{0;1;2;3;4}` which are the indexes into the scaled bin counts array, `{100000;0;400000;0;0}`
• `{100000;0;400000;0;0}+{0;1;2;3;4}``{100000;1;400002;3;4}`, the modified bin counts array
• `MAX({100000;1;400002;3;4})``400002`, the longest sequence modified bin count
• `MOD(400002,10^5)``2`, the rank of the lower threshold of the longest sequence bin in the thresholds array (indexes into the bin counts array correspond to the rank of the lower threshold in the thresholds array)
• `IF(A3:I3>=K3,COLUMN(A3:I3))``{FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9}`, the thresholds array
• `SMALL({FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9},2)+1``3+1``4`, the column number of the first cell of the longest sequence (`SMALL()` ignores boolean values; the lower threshold corresponding to a bin is the column number of the cell just before the first cell of the bin)
• `IFERROR(SMALL(…)+1,COLUMN(A3:I3))` is required as there is no lower threshold for the first bin and if the longest sequence corresponds to the first bin (i.e., the longest sequence starts at the first cell of the data range) we get `SMALL({…},0)+1``#NUM!`. `IFERROR()` traps this error, and `COLUMN(A3:I3)` returns the column number of the first cell.
• `INDEX((1:1),4)``4`, the column header of the first cell of the longest sequence

Notes:

• The prettified formula actually works if entered.
• The brackets around `(1:1)` are required to force the `1:1` to remain on its own line.
• `ROW(INDEX(column,1):INDEX(column,…))` is used instead of the more common `ROW(INDIRECT("1:"&…))` as it is non-volatile and also still works when rows/columns are deleted. (Provided `column` is set to the column of the cell the formula is entered into, of course.)
• `IF(1,,"comment")` is an inline comment. (The value is always zero so there is no net effect on the formula.)
• If you just wish to display the column number of the first cell, the formula can be simplified by removing the outermost `INDEX()`.

Caveat:

• If more than one longest sequence exists, the above formula returns the start header of the last longest sequence. The formula can be modified to return the start header of the first longest sequence by using the ten's complement of the bin index instead of the plain index when modifying the bin counts:
`{=INDEX(1:1,IFERROR(SMALL(IF(A3:I3>=K3,COLUMN(A3:I3)),10^5-MOD(MAX(10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))+10^5-(ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1)),10^5))+1,COLUMN(A3:I3)))}`