Find first

[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

[Dave Peterson]

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)

DAVID VINCENT-JONES wrote:
>
> 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

--

Dave Peterson

[Domenic]

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!

In article <[email protected]>,
"DAVID VINCENT-JONES" <[email protected]> wrote:

> 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