# Return column header from first cell from consecutive cells below a threshold

• Hello, I've got the formula to identify that largest group of consecutive cells that fall below the 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: =MAX(FREQUENCY(IF(A3:N3<P3,COLUMN(A3:N3)),IF(A3:N3>P3,COLUMN(A3:N3)))) to identify the 'longest consecutive event' and would like cell S3 to show '9', which is the column header for the first cell in the longest consecutive event; and cell S4 to show '2', which is the first cell in the longest consecutive event on that row. Is this possible?

[tr]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[td]

E

[/td]

[td]

F

[/td]

[td]

G

[/td]

[td]

H

[/td]

[td]

I

[/td]

[td]

J

[/td]

[td]

K

[/td]

[td]

L

[/td]

[td]

M

[/td]

[td]

N

[/td]

[td]

O

[/td]

[td]

P

[/td]

[td]

Q

[/td]

[td]

R

[/td]

[td]

S

[/td]

[/tr]

[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="align: right"] 10
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 12
[/TD]
[TD="align: right"] 13
[/TD]
[TD="align: right"] 14
[/TD]

[td]

90th Percentile

[/td]

[td]

Threshold

[/td]

[td]

Number of times data drops below threshold

[/td]

[td]

Longest consecutive event

[/td]

[td]

[/td]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 45
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 39
[/TD]
[TD="align: right"] 50
[/TD]
[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"] 46
[/TD]
[TD="align: right"] 24
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 23
[/TD]
[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"] 8
[/TD]
[TD="align: right"] 4
[/TD]

[td][/td]

[/tr]

[/TABLE]

## Files

• Example.xlsx

• Try Array* formula:

=MATCH(MAX(FREQUENCY(IF(A2:N2<P2,COLUMN(A2:N2)),IF(A2:N2>P2,COLUMN(A2:N2)))),FREQUENCY(IF(A2:N2<P2,COLUMN(A2:N2)),IF(A2:N2>P2,COLUMN(A2:N2))),0)

[arf]*[/arf]

Where there is a will there are many ways. Finding one that works for you is the challenge!

MS Excel MVP 2010-2016

• ,

Try Array* formula:

=MATCH(MAX(FREQUENCY(IF(A2:N2<P2,COLUMN(A2:N2)),IF(A2:N2>P2,COLUMN(A2:N2)))),FREQUENCY(IF(A2:N2<P2,COLUMN(A2:N2)),IF(A2:N2>P2,COLUMN(A2:N2))),0)

[arf]*[/arf]

Thank you for this.

I'm applying it to my larger dataset to see if it works (it works for the example given).

• Try Array* formula:

=MATCH(MAX(FREQUENCY(IF(A2:N2<P2,COLUMN(A2:N2)),IF(A2:N2>P2,COLUMN(A2:N2)))),FREQUENCY(IF(A2:N2<P2,COLUMN(A2:N2)),IF(A2:N2>P2,COLUMN(A2:N2))),0)

[arf]*[/arf]

This works for the example given but not for my larger dataset (example below).

The correctly returned column headers should be: 9, 2, 6, 3, 4, 8, 9, 11, 2

[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 816"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[td]

E

[/td]

[td]

F

[/td]

[td]

G

[/td]

[td]

H

[/td]

[td]

I

[/td]

[td]

J

[/td]

[td]

K

[/td]

[td]

L

[/td]

[td]

M

[/td]

[td]

N

[/td]

[td]

O

[/td]

[td]

P

[/td]

[td]

Q

[/td]

[td]

R

[/td]

[/tr]

[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="align: right"] 10
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 12
[/TD]
[TD="align: right"] 13
[/TD]
[TD="align: right"] 14
[/TD]

[td]

90th Percentile

[/td]

[td]

Threshold

[/td]

[td]

Number of times data drops below threshold

[/td]

[td]

Longest consecutive event

[/td]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 45
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 39
[/TD]
[TD="align: right"] 50
[/TD]
[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]

[/tr]

[tr]

[TD="align: right"] 46
[/TD]
[TD="align: right"] 24
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 23
[/TD]
[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"] 8
[/TD]
[TD="align: right"] 4
[/TD]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 22
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 23
[/TD]
[TD="align: right"] 22
[/TD]
[TD="align: right"] 21
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 22
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 5
[/TD]
[TD="align: right"] 3
[/TD]

[/tr]

[tr]

[TD="align: right"] 24
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 23
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/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]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 10
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 17
[/TD]
[TD="align: right"] 18
[/TD]
[TD="align: right"] 21
[/TD]
[TD="align: right"] 39
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 33
[/TD]
[TD="align: right"] 35
[/TD]
[TD="align: right"] 40
[/TD]
[TD="align: right"] 50
[/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]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 12
[/TD]
[TD="align: right"] 13
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 12
[/TD]
[TD="align: right"] 13
[/TD]
[TD="align: right"] 14
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 7
[/TD]
[TD="align: right"] 4
[/TD]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 11
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 7
[/TD]
[TD="align: right"] 4
[/TD]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 43
[/TD]
[TD="align: right"] 41
[/TD]
[TD="align: right"] 35
[/TD]
[TD="align: right"] 32
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 29
[/TD]
[TD="align: right"] 28
[/TD]
[TD="align: right"] 27
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 26
[/TD]
[TD="align: right"] 26
[/TD]
[TD="align: right"] 27
[/TD]
[TD="align: right"] 28
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 30
[/TD]
[TD="align: right"] 8
[/TD]
[TD="align: right"] 4
[/TD]

[/tr]

[tr]

[TD="align: right"] 50
[/TD]
[TD="align: right"] 25
[/TD]
[TD="align: right"] 22
[/TD]
[TD="align: right"] 23
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 22
[/TD]
[TD="align: right"] 40
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 40
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/TD]
[TD="align: right"] 50
[/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]

[/tr]

[/TABLE]

## Files

• Example.xlsx