Function to find the first cell that is not zero
 

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do this?

Mark

Function to find the first cell that is not zero
 

On Sat, 9 Jun 2007 06:14:20 +1000, "Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do this?

Mark



You only need to post this question once.
--ron

Function to find the first cell that is not zero
 

This can be done with a formula, but, what kind of result do you want? Do
you want the value itself? Do you want the cell address? Do you want the
relative position of the value? Is the data numeric? Are there any negative
numbers? Are there any formulas in the range that return formula blanks?

Lots of things to consider for the best solution!

Biff

"Mark" wrote in message
...
Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

Try this:
Assume your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<0,0))

ctrl+shift+enter, not just enter


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do this?

Mark

Function to find the first cell that is not zero
 

On Fri, 8 Jun 2007 15:31:00 -0700, Teethless mama
wrote:

Try this:
Assume your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<0,0))

ctrl+shift+enter, not just enter


Of course, that will match on a text entry also. I guess that is non-zero, as
the OP requested.
--ron

Function to find the first cell that is not zero
 

I know Ron, sorry but I had a wireless net failure. It happens.



"Ron Rosenfeld" wrote in message
...
On Sat, 9 Jun 2007 06:14:20 +1000, "Mark"
wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do
this?

Mark



You only need to post this question once.
--ron

Function to find the first cell that is not zero
 

Yes good question. The ideal for my situation would be the value of the
first instance.

Many thanks.


"T. Valko" wrote in message
...
This can be done with a formula, but, what kind of result do you want? Do
you want the value itself? Do you want the cell address? Do you want the
relative position of the value? Is the data numeric? Are there any
negative numbers? Are there any formulas in the range that return formula
blanks?

Lots of things to consider for the best solution!

Biff

"Mark" wrote in message
...
Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

On Mon, 11 Jun 2007 10:22:41 +1000, "Mark"
wrote:

I know Ron, sorry but I had a wireless net failure. It happens.




Answered in your other thread:


---------------------------------
What do you mean by "find" the first cell?

This **array-entered** formula will return the column number of the first cell
in row 1 with a non-zero entry (enter with <ctrl-shift-enter)

=MATCH(1,ISNUMBER(1:1)*(1:1<0),0)

This **array-entered** formula will return the value in that cell:


=INDIRECT(ADDRESS(1,MATCH(1,ISNUMBER(1:1)*(1:1<0) ,0)))
-------------------------------
--ron

Function to find the first cell that is not zero
 

Try this:

Assuming the target row is row 1.

=INDEX(1:1,MATCH(1,INDEX(--(1:1<0),,),0))

This will return the data from the first cell (left to right) that is not a
numeric 0.

Biff

"Mark" wrote in message
...
Yes good question. The ideal for my situation would be the value of the
first instance.

Many thanks.


"T. Valko" wrote in message
...
This can be done with a formula, but, what kind of result do you want? Do
you want the value itself? Do you want the cell address? Do you want the
relative position of the value? Is the data numeric? Are there any
negative numbers? Are there any formulas in the range that return formula
blanks?

Lots of things to consider for the best solution!

Biff

"Mark" wrote in message
...
Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

Many thanks. However, this is giving me a circular reference error when I
place it the first column of the row.

Any solution to this?

Mark


"T. Valko" wrote in message
...
Try this:

Assuming the target row is row 1.

=INDEX(1:1,MATCH(1,INDEX(--(1:1<0),,),0))

This will return the data from the first cell (left to right) that is not
a numeric 0.

Biff

"Mark" wrote in message
...
Yes good question. The ideal for my situation would be the value of the
first instance.

Many thanks.


"T. Valko" wrote in message
...
This can be done with a formula, but, what kind of result do you want?
Do you want the value itself? Do you want the cell address? Do you want
the relative position of the value? Is the data numeric? Are there any
negative numbers? Are there any formulas in the range that return
formula blanks?

Lots of things to consider for the best solution!

Biff

"Mark" wrote in message
...
Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

Many thanks. However, this is also giving me a circular reference error when
I place it the first column of the row.

Mark

"Teethless mama" wrote in message
...
Try this:
Assume your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<0,0))

ctrl+shift+enter, not just enter


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

Oh, its ok I've fixed it. I've simply specified the cells (b1:iq1) for the
array. Many thanks.
Mark

"Mark" wrote in message
...
Many thanks. However, this is giving me a circular reference error when I
place it the first column of the row.

Any solution to this?

Mark


"T. Valko" wrote in message
...
Try this:

Assuming the target row is row 1.

=INDEX(1:1,MATCH(1,INDEX(--(1:1<0),,),0))

This will return the data from the first cell (left to right) that is not
a numeric 0.

Biff

"Mark" wrote in message
...
Yes good question. The ideal for my situation would be the value of the
first instance.

Many thanks.


"T. Valko" wrote in message
...
This can be done with a formula, but, what kind of result do you want?
Do you want the value itself? Do you want the cell address? Do you want
the relative position of the value? Is the data numeric? Are there any
negative numbers? Are there any formulas in the range that return
formula blanks?

Lots of things to consider for the best solution!

Biff

"Mark" wrote in message
...
Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero
 

Oh, its ok I've fixed it. I've simply specified the cells (b1:iq1) for the
array. Many thanks.
Mark

"Mark" wrote in message
...
Many thanks. However, this is also giving me a circular reference error
when I place it the first column of the row.

Mark

"Teethless mama" wrote in
message ...
Try this:
Assume your data in row 1

=INDEX(1:1,MATCH(TRUE,1:1<0,0))

ctrl+shift+enter, not just enter


"Mark" wrote:

Hi

I want to find the first cell that is not zero "0" across a row. I am
also
trying a VBA solution, but is there perchance a function that will do
this?

Mark

Function to find the first cell that is not zero-Thanks everyone
 

A hearty thanks to everyone for your kind help and support. Much
appreciated!!!

These solutions all work nicely.

Mark


"Mark" wrote in message
...
Hi

I want to find the first cell that is not zero "0" across a row. I am also
trying a VBA solution, but is there perchance a function that will do
this?

Mark