Find first

  • Thread starter Thread starter DAVID VINCENT-JONES
  • Start date Start date
D

DAVID VINCENT-JONES

I have a column that has multiple zero values at the top and some negative
and zero values below.

I want to find the first (topmost) non-zero value. It will be negative.

What would be the appropriate formula to use?

Thanks;

David
 
One way (assumes data is in A1:A25):

=INDEX(A1:A25,MATCH(TRUE,A1:A25<>0,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Maybe (just in case):

=IF(COUNTIF(A1:A25,"<>"&0)=0,"No non-zeros",
INDEX(A1:A25,MATCH(TRUE,A1:A25<>0,0)))

(all one cell and still an array formula)
 
Here's another way...

=INDEX(A1:A10,MATCH(TRUE,INDEX((A1:A10<>0),0),0))

....which needs to be confirmed with just ENTER.

Hope this helps!
 
Back
Top