Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-30-2018, 01:29 AM
zara007 zara007 is offline Return column header from first cell from consecutive cells below a threshold Windows 7 32bit Return column header from first cell from consecutive cells below a threshold Office 2010 32bit
Novice
Return column header from first cell from consecutive cells below a threshold
 
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, 8 views)
Reply With Quote
  #2  
Old 05-30-2018, 04:54 AM
NBVC's Avatar
NBVC NBVC is offline Return column header from first cell from consecutive cells below a threshold Windows 10 Return column header from first cell from consecutive cells below a threshold Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Crossposted


Please read here: https://www.excelguru.ca/content.php?184
Reply With Quote
  #3  
Old 05-30-2018, 05:55 AM
zara007 zara007 is offline Return column header from first cell from consecutive cells below a threshold Windows 7 32bit Return column header from first cell from consecutive cells below a threshold Office 2010 32bit
Novice
Return column header from first cell from consecutive cells below a threshold
 
Join Date: May 2018
Posts: 5
zara007 is on a distinguished road
Default Sorry, i didn't realise i shouldn't post elsewhere...

Quote:
Originally Posted by NBVC View Post
Crossposted


Please read here: https://www.excelguru.ca/content.php?184

I haven't got a solution yet and I will post the solution if it gets solved.
Reply With Quote
  #4  
Old 05-30-2018, 06:04 AM
NBVC's Avatar
NBVC NBVC is offline Return column header from first cell from consecutive cells below a threshold Windows 10 Return column header from first cell from consecutive cells below a threshold Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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
Reply With Quote
  #6  
Old 05-30-2018, 06:31 AM
zara007 zara007 is offline Return column header from first cell from consecutive cells below a threshold Windows 7 32bit Return column header from first cell from consecutive cells below a threshold Office 2010 32bit
Novice
Return column header from first cell from consecutive cells below a threshold
 
Join Date: May 2018
Posts: 5
zara007 is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
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

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).
Reply With Quote
  #7  
Old 05-30-2018, 07:07 AM
zara007 zara007 is offline Return column header from first cell from consecutive cells below a threshold Windows 7 32bit Return column header from first cell from consecutive cells below a threshold Office 2010 32bit
Novice
Return column header from first cell from consecutive cells below a threshold
 
Join Date: May 2018
Posts: 5
zara007 is on a distinguished road
Default

Quote:
Originally Posted by NBVC View Post
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

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).
Attached Files
File Type: xlsx Example.xlsx (10.0 KB, 11 views)
Reply With Quote
Reply

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
Return column header from first cell from consecutive cells below a threshold have a formula that combines two cells return a zero instead of a blank cell Kubi Excel 3 08-07-2017 11:52 PM
Return column header from first cell from consecutive cells below a threshold 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft