Thread: Return column header from first cell from consecutive cells below a threshold
View Single Post
 
Old 05-30-2018, 01:29 AM
zara007 zara007 is offline Windows 7 32bit Office 2010 32bit
Novice
  
Join Date: May 2018
Posts: 5
zara007 is on a distinguished road
Question 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 a threshold in a row of data and would like to show the column header of the first cell. Example data is attached.

I'm using a CSE (array) formula: =MAX(FREQUENCY(IF(A2:N2<P2,COLUMN(A2:N2)),IF(A2:N2 >P2,COLUMN(A2:N2)))) to identify the 'longest consecutive event' and would like cell S2 to show '9', which is the column header for the first cell in the longest consecutive event; and cell S3 to show '2', which is the first cell in the longest consecutive event on that row. Is this possible?
Attached Files
File Type: xlsx Example.xlsx (10.4 KB, 10 views)
Reply With Quote