|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
||||
|
||||
|
#3
|
|||
|
|||
Sorry, i didn't realise i shouldn't post elsewhere...
Quote:
I haven't got a solution yet and I will post the solution if it gets solved. |
#4
|
||||
|
||||
We don't need you to post the solution, we need you to post the links to the threads you started in other forums... I found a couple already. It is not fair to the people to not know you have posted elsewhere and possibly have received a solution already. They helpers are not here to waste their time if you have a solution, they are to help if you don't.
By the way, I gave you a solution here: https://www.ozgrid.com/forum/forum/h...ow-a-threshold |
#6
|
|||
|
|||
Quote:
Thanks for this: 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,C OLUMN(A2:N2)),IF(A2:N2>P2,COLUMN(A2:N2))),0) I'm applying it to my larger dataset to see if it works (it works for the example given). |
#7
|
|||
|
|||
Quote:
Thank you for taking the time to look at this. This worked for my initial example given but not for my larger dataset (example attached). |
Tags |
formula, header, match |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How To Find Highest Value In A Row And Return Column Header | ballpoint | Excel | 1 | 02-23-2018 11:18 AM |
have a formula that combines two cells return a zero instead of a blank cell | Kubi | Excel | 3 | 08-07-2017 11:52 PM |
If value of cell A Matches a value in a Range of cells (column) then add value of cell A to cell C | rick10r | Excel | 1 | 07-05-2016 12:07 PM |
Find text within cell and return column and row title next to the name on a new sheet. | tanyabowring@live.co.uk | Excel Programming | 2 | 03-26-2015 01:48 AM |
Return Sum value of one column from cells not blank in another column | zulugandalf | Excel | 3 | 08-14-2014 03:37 AM |