Determine the maximum number of consecutive 1’s appearing in a range

{ 26 Comments }

By Ashish Mathur · · · Leave a comment

Assume a database where customers are listed from cell A6 down.  From cell B5 to the right months are entered from April to March (B5:M5).  In B6:M6 (Customer 1), a user enters 1’s and 0’s.  A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”.  Similar data is entered for other customers in B7:M500.

One may want to know the maximum number of consecutive “Cheque bounce events” for all customers listed in column A without using spare rows and columns.

In cell N6, enter the following array formula (Ctrl+Shift+Enter)

=IF(MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6))))=1,0,MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6)))))

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Hi,

    Attached file shows the status of Meter-daywise. It has three status as follows…

    * “F” with Red font stating faulty meter.
    * “Numbers/Readings” with Green font stating Working.
    * “NI” for Meter Not Installed.

    I need to calculate unique number of fautly times that meter reported.

    For eg:

    TAG ID 1 2 3 4 5 6 7 8 9 10 11 Faulty Status
    HAGT10547_A 1480 F F 1520 F 1540 F F 3

    For this above site, the Meter was working on 1st.
    Then on 2nd it went Faulty & it continues to be faulty on 3rd too. This means meter went faulty 1 time(Actual Faulty)
    On 5th, it shown reading which means the meter got repaired & working on 5th.
    Again on 6th, meter went faulty. Now it goes to second time faulty.
    On 8th, it shown reading which means the meter got repaired & working on 8th.
    Then on 9th it went Faulty. On 10th no readings taken. Then it continues to be faulty on 11th too. This means meter went faulty 1 time
    So the actual faulty count should be “3”.
    If I use count formula, I’ll get 5 faulty count. But I need “3” here.

    Request you to help me out…

    Reply

    • Hi,

      Your data in the table does not appear very clearly. Assume that data is arranged as follows:

      1. Cell A5 has the text value “TAG ID”;
      2. Range B5:L5 have numbers from 1 to 11;
      3. Cell M5 has the text value “Faulty Status”;
      4. Cell B6 has 1480;
      5. Range C6:D6 has F;
      6. Cell F6 has 1520;
      7. Cell G6 has F;
      8. Cell I6 has 1540;
      9. Cell J6 has F; and
      10. Cell L6 has F

      In cell M6, you wish to know the “Unique consecutive” occurences of “F”. In this case, answer should be 3.

      In cell M6, try this array formula (Ctrl+Shift+Enter)

      =SUMPRODUCT(1*(FREQUENCY(IF(B6:L6=”F”,COLUMN(B6:L6)),IF(ISNUMBER(B6:L6),COLUMN(B6:L6)))>0))

      Hope this helps.

      Reply

  • Hi,

    I have a sheet with four main categories. (this is for a card game, BTW)

    Wins (W), 2nd (S), 3rd (T) and the loss is shown as a number (1 – 200+).
    Also, there are special Wins (W3) and special 2nd (S3).

    I need to calculate the longest winning streak with the W as well as the W3.
    I need to calculate the longest winless streak to include S, S3, T and # (loss)
    The highest Winless streak is a combination of S, S*, T and # (the highest consecutive number of games without a win).
    Then I need the longest losing streak counting the most cells in a row with a number.

    It looks a little like this –
    A B C D E F G H I J K L M N O P Q R S T U V W X Y
    1 Peter T T S S W WW W3 W T W T W T W T
    2 Kevin 50 W W S W 45 T T S3 S W3 65 T 50 W3 S S S W
    3 Josh S W S S T T S3 W T W 96 S

    Peter should have a winning streak of 5, a winless streak of 4 and a losing streak of 0
    Kevin should have a winning streak of 2, a winless streak of 5 and a losing streak of 1.
    Josh should have a winning streak of 1, a winless streak of 5 and a losing streak of 1

    Any advice will be greatly appreciated. It’s getting pretty painful figuring this out manually. 🙂

    Thanks,

    Josh.

    Reply

      • Hi Ashish,

        Thank you very much. That appears to be working.
        I had a little glitch, but figured it out.
        All of the cells calculate the final results from another worksheet. If a player doesn’t play the false logic value was shown as a space ” ” , displaying a blank cell. I changed the value “” (no space) to show nothing, and everything else fell into place.

        Nice job!

        I may pick your brain again someday. 🙂

        Reply

      • Ashish, I am trying something similar, and so tried to adapt your array formula, but not understanding it, couldn’t make it work.

        Understanding statistics is important for my students, and so I have asked them all to record as many coin tosses as they can bear (at least 100) which I plan to aggregate to analyze patterns in coin tosses.

        So I have a single column with n rows with a string of “T”s and “H”s indicating the outcome of n tosses. I want to search for the longest streaks of either toss. This is what I tried for column with 100 tosses:

        =SUMPRODUCT(1*FREQUENCY(IF(A1:A101="T",ROW(A1:A101)),IF(ISNUMBER(A1:A101),ROW(A1:A101)))>0)

        I am running excel 2011 for mac v. 14.3.2 on mac osx 10.6.8

        Reply

          • Hi,

            I am not sure of what is the Mac equivalent of Ctrl+Shift+Enter (Array formula). When I array enter (Ctrl+Shift+Enter) these formulas in cell D4:D5 of sheet2, I get the result as 6 for Tails (range A21:A26) and 9 for Heads (range A74:A82).

            Array formula used in cell D4:

            =IF(MAX(FREQUENCY(IF(A1:A100=”T”,ROW(A1:A100)),IF(A1:A100=”H”,ROW(A1:A100))))=”T”,0,MAX(FREQUENCY(IF(A1:A100=”T”,ROW(A1:A100)),IF(A1:A100=”H”,ROW(A1:A100)))))

            Array formula used in cell D5:

            =IF(MAX(FREQUENCY(IF(A1:A100=”H”,ROW(A1:A100)),IF(A1:A100=”T”,ROW(A1:A100))))=”H”,0,MAX(FREQUENCY(IF(A1:A100=”H”,ROW(A1:A100)),IF(A1:A100=”T”,ROW(A1:A100)))))

            Once again, these formulas have to be confirmed with Ctrl+Shift+Enter

            Hope this helps.

        • Hi,

          Try this array formula to count the maximum streak of H’s

          =IF(MAX(FREQUENCY(IF(A1:A101="T",ROW(A1:A101)),IF(A1:A101="H",ROW(A1:A101))))="T",0,MAX(FREQUENCY(IF(A1:A101="T",ROW(A1:A101)),IF(A1:A101="H",ROW(A1:A101)))))

          Reply

  • I’m posting this comment as you asked me to:

    Hi! I have this data on range C2:C29

    1 0 0 0 1 1 0 1 1 1 1 0

    I need to find the largest number of consecutive repetitions on a series

    So for this data it would be number, which repeats 4 times in a row.

    NOTE: So I’m not looking which repeats the most in general (cause in that case I would look for nuber 1 which repeats 8 times), but in a consecutive way (which is number 1, which repeats 4 times).

    NOTE: For the record, I’ve tried with sumproduct and countif with no luck. Maybe I’m not doing it right!

    Thank you so much for your time!!

    Dani

    Reply

    • Hi,

      Assuming you have only 1’s and 0’s in the range, enter this array formula (Ctrl+Shift+Enter) in cell E2

      =MAX(IF(MAX(FREQUENCY(IF(C2:C13=1,ROW(C2:C13)),IF(C2:C13=0,ROW(C2:C13))))=1,0,MAX(FREQUENCY(IF(C2:C13=1,ROW(C2:C13)),IF(C2:C13=0,ROW(C2:C13))))),IF(MAX(FREQUENCY(IF(C2:C13=0,ROW(C2:C13)),IF(C2:C13=1,ROW(C2:C13))))=0,0,MAX(FREQUENCY(IF(C2:C13=0,ROW(C2:C13)),IF(C2:C13=1,ROW(C2:C13))))))

      Hope this helps.

      Reply

  • Hello,

    As requested, I am reposting this question from another forum.

    I am looking to do this exact same function, but I need to ignore blank cells BEFORE the first populated cell.

    Empty Empty Empty Data Empty Data Empty Empty Data

    For the above example, I would want the result to be ” 2 ” because after the first populated cell, there is a maximum of 2 empty cells between the remaining data. Can this be done with a UDF?

    Reply

  • Hai
    I have a data of O O P P P P O O P P P O O P P
    I want to calculate the max number of continuous P which occur last in the row. I want the answer a 2.
    I have checked this formula but it is working only for Numbers
    =MAX(0,LOOKUP(2,1/(B2:BA2=1),COLUMN(B2:BA2))-LOOKUP(2,1/(ISNUMBER(B2:BA2)*(B2:BA2=0)),COLUMN(B2:BA2)))

    Could you please suggest with example.

    Thank you in advance

    Reply

    • Hi,
      Does this work?
      IF(MAX(FREQUENCY(IF(B6:M6=”P”,COLUMN(B6:M6)),IF(B6:M6=”O”,COLUMN(B6:M6))))=1,0,MAX(FREQUENCY(IF(B6:M6=”P”,COLUMN(B6:M6)),IF(B6:M6=”O”,COLUMN(B6:M6)))))

      Reply

      • Thanks for the reply.
        The above suggested formula is not working.
        It is showing as error #Value.
        Please suggest another way

        Reply

          • Link Sharing is not possible in our company.

            Please find the data mentioned Below.

            I have used the same formula as you mentioned
            =IF(MAX(FREQUENCY(IF(B10:O10=”P”,COLUMN(B10:O10)),IF(B10:O10=”O”,COLUMN(B10:O10))))=1,0,MAX(FREQUENCY(IF(B10:O10=”P”,COLUMN(B10:O10)),IF(B10:O10=”O”,COLUMN(B10:O10)))))

            These are the Values
            O O P P P O O P P P O O P P

            The result is coming as #VALUE

            Actually the result that i required is 2 because at the End 2 P available continuously . If I kept O at last means the count should reset and should show as 0.

            Please suggest a solution.