Results 1 to 2 of 2

Indicate the Instance of Consecutive Zeros in a Row

Threaded View

  1. #1
    Registered User
    Join Date
    08-21-2014
    Location
    Atlanta, Georgia, USA
    MS-Off Ver
    2010
    Posts
    13

    Indicate the Instance of Consecutive Zeros in a Row

    Hello,

    I am trying to figure out a formula where an indicator of 1 is put at the end of a row that contains an instance of seven consecutive zeros in that same row. Also the number of times there are seven consecutive zeros does not matter, if there is one instance then add a 1 indicator, if there are 5 instances then a 1 indicator is still fine.

    So in the sample I have attached, rows 3 and 6 would receive a 1 indicator, and the rest would receive nothing or a 0 is fine as well.

    Thanks for any help.

    excel_help_sample.xlsx

    EDIT: I have this so far. This is a modified version of a formula I found else where on the forum that pretty much works for what I'm trying to accomplish.

    =SUMPRODUCT(--(COUNTIF(OFFSET(A6,0,ROW(INDIRECT("1:"&MATCH(9.99999999999999E+307,A6:V6)-6))-1,1,7),0)=7))
    However, I'm not 100% such exactly what its doing, so an explanation would be great
    Last edited by IbeforeV; 02-04-2015 at 03:20 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count of Consecutive Zeros excluding #N/A
    By gaffee in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-10-2014, 10:04 AM
  2. Count longest consecutive run of zeros
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2013, 08:02 AM
  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. Pull every instance INDEX/MATCH criteria into consecutive rows
    By PowerSchoolDude in forum Excel General
    Replies: 4
    Last Post: 01-13-2010, 06:42 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