Function to find the first cell that is not zero

M

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
 
R

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
Click to expand...

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

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
 
T

Teethless mama

Try this:
Assume your data in row 1

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

ctrl+shift+enter, not just enter
 
R

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
Click to expand...


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

Mark

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

Many thanks.
 
R

Ron Rosenfeld

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

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

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
 
M

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
 
M

Mark

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

Mark
 
M

Mark

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

Mark

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

Mark

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

These solutions all work nicely.

Mark
 
Top