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

[DataDog]

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!

[Andrew-R]

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.

[zbor]

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...

[AndyPS]

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.

[DataDog]

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!