Is there a function, or combination of nested functions to count the number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to know the
number of zeros in a row until I encounter the first nonzero cell.

Try
=MATCH(0.0001,1:1)
OR
=MATCH(0.0001,A1:J1)

If this post helps click Yes
---------------
Jacob Skaria


"Bubba" wrote:

Is there a function, or combination of nested functions to count the number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to know the
number of zeros in a row until I encounter the first nonzero cell.

Bubba wrote:
Is there a function, or combination of nested functions to count the number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to know the
number of zeros in a row until I encounter the first nonzero cell.


Try the following array formula (commit with CTRL+SHIFT+ENTER):

=MATCH(1,--(A1:K10),0)

That will return the result you specified, but not the answer to your original
question. Subtract one to answer that question.

Will there *always* be a non-zero entry to find? Are the non-zero numbers
*always* positive numbers greater than 0? Are there any TEXT entries in the
range? Are there any formula blanks in the range?

--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
Is there a function, or combination of nested functions to count the
number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to know
the
number of zeros in a row until I encounter the first nonzero cell.

There will always be a non-zero number that is positive (greater than zero).
There are no text entries and no formula blanks.

I have tried:

=IF(J6=0,MATCH(0.0001,J6:BM6),0)

but the result is not always correct. Sometimes the formula counts
correctly, other times it returns a number equal to the range aka if data is
in columns J:BM it might sometimes returns '56' even though a nonzero number
was encountered before Column BM


"T. Valko" wrote:

Will there *always* be a non-zero entry to find? Are the non-zero numbers
*always* positive numbers greater than 0? Are there any TEXT entries in the
range? Are there any formula blanks in the range?

--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
Is there a function, or combination of nested functions to count the
number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to know
the
number of zeros in a row until I encounter the first nonzero cell.

The ealier formula works only if the 1st cell starts with zero...If the 1st
cell do not have a zero then try the below

=MATCH(0.1,A6:J6)-MATCH(0,A6:J6,0)+1

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try
=MATCH(0.0001,1:1)
OR
=MATCH(0.0001,A1:J1)

If this post helps click Yes
---------------
Jacob Skaria


"Bubba" wrote:

Is there a function, or combination of nested functions to count the number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to know the
number of zeros in a row until I encounter the first nonzero cell.

Ok, based on this:

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30
For the First Row I want the function to return the value
6 and the second row to return the value '7'

What you want to do is find the position of the first non-zero entry. In row
1 the first non-zero entry is located at position 6 and for row 2 the first
non-zero entry is located at position 7.

Try this:

=MATCH(TRUE,INDEX(J1:BM10,0),0)


--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
There will always be a non-zero number that is positive (greater than
zero).
There are no text entries and no formula blanks.

I have tried:

=IF(J6=0,MATCH(0.0001,J6:BM6),0)

but the result is not always correct. Sometimes the formula counts
correctly, other times it returns a number equal to the range aka if data
is
in columns J:BM it might sometimes returns '56' even though a nonzero
number
was encountered before Column BM


"T. Valko" wrote:

Will there *always* be a non-zero entry to find? Are the non-zero numbers
*always* positive numbers greater than 0? Are there any TEXT entries in
the
range? Are there any formula blanks in the range?

--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
Is there a function, or combination of nested functions to count the
number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the
second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to
know
the
number of zeros in a row until I encounter the first nonzero cell.

Perfect, thank you very much!

"T. Valko" wrote:

Ok, based on this:

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30
For the First Row I want the function to return the value
6 and the second row to return the value '7'

What you want to do is find the position of the first non-zero entry. In row
1 the first non-zero entry is located at position 6 and for row 2 the first
non-zero entry is located at position 7.

Try this:

=MATCH(TRUE,INDEX(J1:BM10,0),0)


--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
There will always be a non-zero number that is positive (greater than
zero).
There are no text entries and no formula blanks.

I have tried:

=IF(J6=0,MATCH(0.0001,J6:BM6),0)

but the result is not always correct. Sometimes the formula counts
correctly, other times it returns a number equal to the range aka if data
is
in columns J:BM it might sometimes returns '56' even though a nonzero
number
was encountered before Column BM


"T. Valko" wrote:

Will there *always* be a non-zero entry to find? Are the non-zero numbers
*always* positive numbers greater than 0? Are there any TEXT entries in
the
range? Are there any formula blanks in the range?

--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
Is there a function, or combination of nested functions to count the
number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the
second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to
know
the
number of zeros in a row until I encounter the first nonzero cell.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
Perfect, thank you very much!

"T. Valko" wrote:

Ok, based on this:

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30
For the First Row I want the function to return the value
6 and the second row to return the value '7'

What you want to do is find the position of the first non-zero entry. In
row
1 the first non-zero entry is located at position 6 and for row 2 the
first
non-zero entry is located at position 7.

Try this:

=MATCH(TRUE,INDEX(J1:BM10,0),0)


--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
There will always be a non-zero number that is positive (greater than
zero).
There are no text entries and no formula blanks.

I have tried:

=IF(J6=0,MATCH(0.0001,J6:BM6),0)

but the result is not always correct. Sometimes the formula counts
correctly, other times it returns a number equal to the range aka if
data
is
in columns J:BM it might sometimes returns '56' even though a nonzero
number
was encountered before Column BM


"T. Valko" wrote:

Will there *always* be a non-zero entry to find? Are the non-zero
numbers
*always* positive numbers greater than 0? Are there any TEXT entries
in
the
range? Are there any formula blanks in the range?

--
Biff
Microsoft Excel MVP


"Bubba" wrote in message
...
Is there a function, or combination of nested functions to count the
number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the
second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to
know
the
number of zeros in a row until I encounter the first nonzero cell.

Hi,

Try this

=MATCH(TRUE,INDEX(E29:L290,,),0)-1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Bubba" wrote in message
...
Is there a function, or combination of nested functions to count the
number
of zeros in a row up until the first non-zero valued cell?

For Example

0 0 0 0 0 35 0 50 60
0 0 0 0 0 0 40 10 0 0 30

For the First Row I want the function to return the value 6 and the second
row to return the value '7'

I do not want to know the total number of zeros in a row, I want to know
the
number of zeros in a row until I encounter the first nonzero cell.