0

I have a formula that calculates the length of the longest group of consecutive cells in a row of data that fall below a certain threshold.

I would like to show the column header of the first cell of that group. So in the example below I would like cell N3 to display 4, which is the value of the column header cell D1. Is this possible?

Example Data:

   | A | B | C | D | E | F | G | H | I | J | K         | L | M | N |
---+---+---+---+---+---+---+---+---+---+---+-----------+---+---+---+
 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | X | Threshold | y*| z*| h*| ...[header]
 2 |   |   |   |   |   |   |   |   |   |   |           |   |   |   |
 3 | 20| 52| 61| 23| 18| 25| 25| 40| 42| X | 30        | 5 | 4 | ? | ...[data]


y* -> The number of times the data drops below the threshold, calculated with the formula:

=FREQUENCY(A3:I3,K3)

z* -> The length of the longest consecutive set of cells below the threshold, calculated with the CSE (array) formula:

{=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>K3,COLUMN(A3:I3))))}

h* -> Required formula to return the column header of the first cell of the longest consecutive set of cells.


Links to cross posted sites:

https://www.mrexcel.com/forum/excel-questions/1057479-excel-formula-return-column-header-first-cell-consecutive-cells-below-threshold.html#post5077811

https://www.excelguru.ca/forums/showthread.php?9046-Return-column-header-from-first-cell-from-consecutive-cells-below-a-threshold

https://www.ozgrid.com/forum/forum/help-forums/excel-formulas/1203930-return-column-header-from-first-cell-from-consecutive-cells-below-a-threshold

https://www.excelguru.ca/forums/showthread.php?9046-Return-column-header-from-first-cell-from-consecutive-cells-below-a-threshold&p=37149&posted=1#post37149

https://www.mrexcel.com/forum/excel-questions/1057446-return-column-header-first-cell-identified-consecutive-cells-meet-criteria.html

http://www.msofficeforums.com/newreply.php?do=newreply&noquote=1&p=129061

  • So the answer should be M? – DavidPostill May 30 '18 at 11:59
  • The answer should be '4', which is the header for the first cell in the longest series of consecutive cells below the 'threshold' (which is '30' in the example above). – zara007 May 30 '18 at 12:41
  • Column 'M' is a calculation of the longest series of consecutive cells below the threshold (30): in the example, these start at column D (23, 18, 25, 25). – zara007 May 30 '18 at 12:44
0

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))-1ROW(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)+13+14, 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)))}

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.