+ Reply to Thread
Results 1 to 3 of 3

counting consecutive cells that are greater than or less than 0

  1. #1
    Registered User
    Join Date
    11-12-2009
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    2

    counting consecutive cells that are greater than or less than 0

    My goal is to look at a column of numbers and count the number of consecutive cells that are positive (or negative), while ignoring blank cells that may be in between them.

    Using the first formula on this thread
    http://www.excelforum.com/excel-gene...-than-0-a.html

    I was able to adapt it to my own data, but whenever I had blank cells in my data the results were not reliable.

    the formula i am using now is
    =IF(COUNT(1/((A1:A27<0)*(A2:A28<0))),MAX(FREQUENCY(IF((A1:A27<0)*(A2:A28<0),ROW(A2:A28)),IF((A1:A27<0)*(A2:A28>=0),ROW(A1:A27))))+1,0)
    for a bunch of data in column A.

    With limited knowledge of excel I'm not exactly sure how this formula works, so I'm not sure how to tell it to ignore all blank cells.

    I attached an example that shows how it fails with the blank cells

    Hopefully someone can help me out!
    Thanks a lot
    Attached Files Attached Files
    Last edited by JHendler; 11-12-2009 at 03:03 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: counting consecutive cells that are greater than or less than 0

    Based on your sample:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-12-2009
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: counting consecutive cells that are greater than or less than 0

    perfect!
    Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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