Let's start by addressing the problems with your two existing formulas.

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.

For your supplied example:

`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)))}

`M`

? – DavidPostill♦ May 30 '18 at 11:59