Function to find the first cell that is not zero

[Mark]

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

 

[Ron Rosenfeld]

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

 

[T. Valko]

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

 

[Guest]

Try this:
Assume your data in row 1

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

ctrl+shift+enter, not just enter

 

[Ron Rosenfeld]

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

 

[Mark]

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

 

[Mark]

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

Many thanks.

 

[Ron Rosenfeld]

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

 

[T. Valko]

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]

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

 

[Mark]

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

Mark

 

[Mark]

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

 

[Mark]

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

 

[Mark]

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

These solutions all work nicely.

Mark