Pick up the first positive (non Zero) number in a range

[masim]

Hi

here is my range starting from say C1.

-1920 -56 -988 0 0 95 25 1222

How can i pick first positive non zero number (in this case 95) ?

Thanks
Asim

 

[Andrew Poulsom]

Not guaranteed, but it works with your sample data (negatives then zeroes then positives):

=INDEX(A1:A7,MATCH(1,A1:A7,1)+1,1)

 

[masim]

My range is horizontal and its not necessary that zeros always follow negative numbers it could be

-1920 -56 -988 95 0 0 25 1222

Sorry for the incomplete post earlier.

Regards
Asim

 

[Haluk]

If the data range is C1:J1 then;

=-MIN(0-C1,0-D1,0-E1,0-F1,0-G1,0-H1,0-I1,0-J1)

 

[masim]

Nopes

This formula captures 1222 as a result. I said at the begining that i want to capture first positive value i.e. 95 in this case.

Regards
Asim

 

[Andrew Poulsom]

If it's horizontal this works with both your examples:

=INDEX(A1:H1,1,MATCH(1,A1:H1,1)+1)

 

[rrdonutz]

From one of Aladin's earlier posts is this ARRAY formula that should work (& I think I've reconstructed it correctly):

=INDEX(C1:J1,MATCH(TRUE,C1:J1>0,0))

--Tom

 

[masim]

Thanks Andrew

Your solution works fine.

Regards
Asim

 

[Aladin Akyurek]

Thanks Andrew

Your solution works fine.

Regards
Asim

I don't think so. I hope Andrew will retract it.

 

[Andrew Poulsom]

Thanks Andrew

Your solution works fine.

Regards
Asim

I don't think so. I hope Andrew will retract it.

I did put a caveat that it wasn't guaranteed. It works with the given data (negatives first). I agree it will not work in every scenario.