+ Reply to Thread
Results 1 to 3 of 3

Consecutive values

  1. #1
    Registered User
    Join Date
    10-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    5

    Consecutive values

    There have been similar problems posted, but none of the solutions have worked for me:

    I have a column H3:H29752 representing True/False by 1 and 0; I would like to know the maximum consecutive run of 1s; in this sample it's 4 ... I would like to know how to set up frequency counts of 4,5,6,7...etc consecutive occurrences ... I could do this in either Excel 2000 or Excel 2013 -which I'm even less familiar with. A short sample from H3:H26 follows ... I have uploaded a sample worksheet called: Consecutive_sample.xls; thank you very much for any assistance!


    0 H3
    1
    0
    1
    0
    1
    0
    0
    0
    0
    0
    1
    0
    1
    1
    1
    1
    0
    0
    1
    0
    1
    1
    0
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Consecutive values

    To get the maximum consecutive 1s you can use this "array formula"

    =MAX(FREQUENCY(IF(H3:H26=1,ROW(H3:H26)),IF(H3:H26<>1,ROW(H3:H26))))

    confirmed with CTRL+SHIFT+ENTER

    to count runs of 3 then use this version

    =SUM(IF(FREQUENCY(IF(H3:H26=1,ROW(H3:H26)),IF(H3:H26<>1,ROW(H3:H26)))=3,1))

    also confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-25-2013
    Location
    New York
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Consecutive values

    Works! Much thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Report max consecutive values
    By craigproudfoot in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-30-2012, 03:42 PM
  2. Count non-consecutive values
    By Kerrigan7 in forum Excel General
    Replies: 2
    Last Post: 11-10-2011, 09:34 AM
  3. Summing Values after Finding Max Consecutive Values
    By pipsturbo in forum Excel General
    Replies: 6
    Last Post: 05-12-2009, 08:06 PM
  4. Return Consecutive Values
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] Return Consecutive Values
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 06-08-2005, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1