+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Ignoring zeros in a range and only counting whole numbers

  1. #1
    Registered User
    Join Date
    02-13-2012
    Location
    Coventry
    MS-Off Ver
    Excel 2007
    Posts
    12

    Ignoring zeros in a range and only counting whole numbers

    Hi folks,

    I'm new to this forum to appolgies if I make any newbie mistakes. The problem I have in Excel 2007 is:

    1. I have a range runnin down column B as below:
    ROW COLUMN B ROW COLUMN D (derived from results of column B)
    1 0 3 1
    2 0 5 3
    3 1
    4 0
    5 3
    6 0
    etc

    What I want to end up with is a function that looks at cell B1, realises it's a zero and counts the number of zeros until it comes across a non-zero and displays that. The in the next cell down it starts at the whole number the previous cell found, and repeats the process of counting zeros utnil the next whole number is found, and on, and on..etc

    I've already spent qutie a while on this and am getting nowhere so any helpful suggestions welcome!

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Ignoring zeros in a range and only counting whole numbers

    Try this formula in column C - it's set up to work on the first 100 rows of column B, but you can expand it to a larger number of rows if required:

    =IF(ROW(1:1)>COUNTIF(B$1:B$100,">0"),"",INDEX(B:B,LARGE(INDEX(ROW(B$1:B$100)*(B$1:B$100>0),0),COUNTIF(B$1:B$100,">0")-(ROW(1:1)-1))))

    PS I met my wife in Coventry, but I won't hold that against you personally.

  3. #3
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Ignoring zeros in a range and only counting whole numbers

    Lol... I'll make printscr of this message and blackmail you :P

    Here is Andrew-R solution (with adition for other criteria)...
    And mine which is simplier but it gives nasty 0's and reverse order...
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-08-2012
    Location
    South Suffolk
    MS-Off Ver
    Excel 2007
    Posts
    102

    Re: Ignoring zeros in a range and only counting whole numbers

    Written prior to earlier posts - may be irrelevant now

    I am not absolutely clear about what the numbers in your example are. The first column items (nos 1 to 6) are presumably the row numbers whilst the second column {0,0,1,0,3,0} are the values in column B. Is the 3rd column of data (a) row numbers in which the result appears or (b) is it the row in which the target value was found? The last column {1,3} appears to be the result. Do these appear in cells D1 and D2?

    It may help if you could explain a bit more about what it is you are trying to do and relevant constraints as there could be other ways of manipulating the data to achieve your desired result. For example, would it be possible to sort the data prior the calculation?

    Does your desired formula have to be a single function or can a column be used to hold additional results such as the row in which the data was found? Also what happens if the value in Bx is non-zero?

    Sorry for bouncing this back but a little expansion on the requirements should aid getting a solution.
    Last edited by AndyPS; 02-13-2012 at 09:51 AM. Reason: Other replies posted before this hit the forum

  5. #5
    Registered User
    Join Date
    02-13-2012
    Location
    Coventry
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Ignoring zeros in a range and only counting whole numbers

    That's worked a treat, thanks very much. I crippled myself walking Hadrian's wall so I guess that makes it one a peice geographically! :o)

    There are one or two other things i need to work out aroud it though I'll set up a seperate thread for them as and when I give up on tryin to figure it out for myself!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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