Count number of consecutive values based on criteria

  • Hello,


    I am looking for a solution that will count the number of hits greater than a value in consecutive columns. Also, looking for the formula to be interchangeable based on the desired number of consecutive occurrences. For example, count the number of times 3 (or more, adjustable) that back to back columns had a value greater than 50%.


    Data sample:


    Each number value is on the same row and specific to each column.


    20% 50% 60% 30% 70% 100% 50% 10%


    If I was targeting 3 consecutive columns greater than 50% I would like the formula to output 1 (based on a count of meeting this criteria). If the target was 2 consecutive months with 50% than the output would be 2.


    Please let me know if this is possible without the use of VBA.

  • Re: Count number of consecutive values based on criteria


    Assuming your data is in A2:H2, then try this Array* formula:


    [COLOR="#0000FF"]=SUM(--((FREQUENCY(IF(A2:H2>=[COLOR="#FF0000"]50[/COLOR],COLUMN(A2:H2)),IF(A2:H2<[COLOR="#FF0000"]50[/COLOR],COLUMN(A2:H2))))>=[COLOR="#FF0000"]3[/COLOR]))[/COLOR]


    If you want the formula to be more dynamic, you can change the 50 and the 3 in the formula to cells containing those values

    [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

  • Re: Count number of consecutive values based on criteria


    Thanks for the reply. I am getting a #VALUE! error.


    My data is all in percentages. Not sure if this affecting the result. I swapped out the 50 to 50%, but it still returned the #VALUE!.


    EDIT:


    I just noticed your comment regarding the confirmation on array formulas. I think this solved it. Appreciate the help!

  • Re: Count number of consecutive values based on criteria


    Quote from Nathan_E;800623

    EDIT:


    I just noticed your comment regarding the confirmation on array formulas. I think this solved it. Appreciate the help!


    Great! :)

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


    MS Excel MVP 2010-2016

  • Re: Count number of consecutive values based on criteria


    It's kind of hard to explain.. have a look at the FREQUENCY() help files, and also use the Evaluate Formula tool in the Formulas tab to step through the formula to see how it is coming up with the results.


    Syntax: FREQUENCY(Data_array,Bins_Array)


    Basically, we are creating the Data Array by returning relative column numbers for where you have a value greater than or equal to 50, we then create the Bins array by returning relative columns numbers for where you have values less than 50.


    Then Frequency compares and counts number of times the first array has an opposite result in the second array (i.e. number in first array and false in other array at same position, false in first array and number is second) and groups them to give a count of each group... then we check those result to see which ones are greater than or equal to your group size of 3.


    Those results are converted to numbers (i.e. False = 0, True = 1) and then summed up to tell you the number of groups that match your criteria...


    Hope that helps.

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


    MS Excel MVP 2010-2016

  • Hello again,


    Is there a way to alter the suggested formula to output the number of times consecutive columns exceed X? Meaning, regardless of the specific cell values when added together and they are strings (side by side).


    Ex. If the cell values exceeds 200% in exactly 4 consecutive columns than count that as 1. Would like the formula to output the number of times this occurs within a dynamic column range.

  • In the original request we were looking for specific values within the columns. In this scenario I am looing for the sum total within consecutive columns and to count the number of occurences.


    Ex. -60%, 80%, 100%, 70%, -30%, 10%. There are no instances of 200% or greater values but those 4 consecutive cells combine for 220%.


    I can achieve this with a long SUM(--if(sum(4 cell range)>200%,1,0),if(sum(next 4 cell range)>200%,1,0)). Wondering if there's a simpler method using the frequency formula.

  • Ok. Thanks for the clarification.


    Assuming range A2:H2, try the following Array* formula


    =SUM(--((FREQUENCY(IF(SUBTOTAL(9,OFFSET(A2,0,COLUMN(A2:H2)-COLUMN(A2),,4))>200,COLUMN(A2:H2)),IF(SUBTOTAL(9,OFFSET(A2,0,COLUMN(A2:H2)-COLUMN(A2),,4))<=200,COLUMN(A2:H2))))))


    [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

  • Thanks for the quick reply. Not sure if it is functioning correctly or taking negative values into account. The below row of data returned a value of 2 with your formula (modified for relevant range). In this example there should not be occurrences of 4 consecutive columns with percentage totals >200%. That I'm seeing anyway..
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 2405"]

    [tr]


    [td]

    -28.9%

    [/td]


    [td]

    -49.2%

    [/td]


    [td]

    -89.8%

    [/td]


    [td]

    1.5%

    [/td]


    [td]

    -32.8%

    [/td]


    [td]

    -15.0%

    [/td]


    [td]

    -65.0%

    [/td]


    [td]

    -15.0%

    [/td]


    [td]

    -68.4%

    [/td]


    [td]

    -61.7%

    [/td]


    [td]

    -46.3%

    [/td]


    [td]

    107.0%

    [/td]


    [td]

    84.0%

    [/td]


    [td]

    -50.9%

    [/td]


    [td]

    40.4%

    [/td]


    [td]

    33.3%

    [/td]


    [td]

    -8.8%

    [/td]


    [td]

    130.0%

    [/td]


    [td]

    -11.5%

    [/td]


    [td]

    -46.9%

    [/td]


    [td]

    94.6%

    [/td]


    [td]

    82.8%

    [/td]


    [td]

    -25.5%

    [/td]


    [td]

    15.1%

    [/td]


    [td]

    -59.4%

    [/td]


    [td]

    -93.2%

    [/td]


    [td]

    -52.6%

    [/td]


    [td]

    -52.6%

    [/td]


    [td]

    -59.4%

    [/td]


    [td]

    -59.4%

    [/td]


    [td]

    27.8%

    [/td]


    [td]

    8.7%

    [/td]


    [td]

    -66.5%

    [/td]


    [td]

    -16.4%

    [/td]


    [td]

    -39.0%

    [/td]


    [td]

    120.5%

    [/td]


    [td]

    -16.0%

    [/td]


    [td]

    15.5%

    [/td]


    [td]

    47.0%

    [/td]


    [td]

    21.0%

    [/td]


    [td]

    76.0%

    [/td]


    [/tr]


    [/TABLE]

  • Are you sure? I get 0 as a result with your numbers....


    Note: You will have to change the 200 occurances in my formula to 200% if your cell entries are actual percentages...

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


    MS Excel MVP 2010-2016

  • Yes, I had changed to % and relevant column range. Its interesting that I am receiving a count of 2 higher than the output from the SUM--(IF formula I was using. Its consistent for lines where I had an output of 3 with the suggested formula I get 5. (or 2 where I had zero). Any theories?

  • Ok,


    The reason for this anomily is you are getting circular references... meaning based on where you put the formulas, the formula ends up referencing the result cell, which causes the circular reference and therefore incorrect result.


    What I would suggest is that you insert 3 blank columns after column AP. You can hide them if you want. This will make sure that when you are looking at 4 cells at time, when it gets to the last 3 cells, then it is not overlapping your formula cells.


    Alternatively you can alter the formula so the ranges are from columns B:AM instead of B:AP, so that the last range of 4 cells looked at is AM:AP


    e.g. =SUM(--((FREQUENCY(IF(SUBTOTAL(9,OFFSET(B2,0,COLUMN(B2:AM2)-COLUMN(B2),,4))>200%,COLUMN(B2:AM2)),IF(SUBTOTAL(9,OFFSET(B2,0,COLUMN(B2:AM2)-COLUMN(B2),,4))<=200%,COLUMN(B2:AM2))))))


    [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

  • Ah, so the offset portion of the formula was referencing cells outside the specified range? I will insert the blank columns as recommended.


    I do have a follow up question, that might be too complex to implement.


    For item A, the output is 3. I was hoping the formula would count that as 1 since the 4 cells that combine for over 200% are all in conjoined columns. I understand that it is counting the other possible combinations but are using the same stringed together cells (AF2,AG2,AH2). Is there a way to count that as 1 occurrence and not stack by reusing cells?

  • Try this Array* formula:


    =SUM(IF((FREQUENCY(IF(SUBTOTAL(9,OFFSET(B2,0,COLUMN(B2:AP2)-COLUMN(B2),,4))>200%,COLUMN(B2:AP2)),IF(SUBTOTAL(9,OFFSET(B2,0,COLUMN(B2:AP2)-COLUMN(B2),,4))<=200%,COLUMN(B2:AP2))))>0,1))


    is that what you mean?


    [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