+ Reply to Thread
Results 1 to 2 of 2

Count longest consecutive run of zeros

  1. #1
    Forum Contributor
    Join Date
    05-26-2004
    Location
    Halifax, UK
    MS-Off Ver
    Office 2016
    Posts
    246

    Count longest consecutive run of zeros

    I have a spreadsheet with daily rainfall data listed in column A. I want to find the longest period of no rainfall - I used to have a spreadsheet somewhere that used an array formula in a column next to the rainfall data and it counted the number of consecutive zero days, so I know it's possible! Can't for the life me remember the formula I used though.

    Example: in column A (daily rainfall data) there is:

    0.5, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 1.5 etc

    So in column B this would read as:

    0, 1, 2, 0, 1, 2, 3, 0

    etc, i.e. basically counting the number of zeros in column A, adding them up until a non-zero is found and starting again.

    Thanks in advance
    -Rob

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Count longest consecutive run of zeros

    Hi,

    I'm sure they'll be a much neater (and non-array) solution, but, assuming your list starts in A2, enter this array formula in B2 and copy down as required:

    =IF(A2<>0,0,MATCH(TRUE,N(OFFSET(A2,-ROW(INDIRECT("1:"&ROWS($1:1))),,,))<>0,0))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ 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. Length of the longest consecutive data series with values not null
    By jacknobody in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-24-2013, 09:42 AM
  2. [SOLVED] how would I find the display excel with longest character count over range
    By bjcowen9000 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2013, 06:07 PM
  3. [SOLVED] Average for non-consecutive cells excluding zeros (even when they all contain zeros)
    By pao13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2012, 04:32 PM
  4. Counting consecutive zeros at end of range
    By darkyam in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-16-2010, 01:45 PM
  5. Counting number of consecutive zeros at the end of a list
    By lee.herring@gmail.com in forum Excel General
    Replies: 2
    Last Post: 09-22-2005, 08:05 AM

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