First Positive

  • Thread starter David Vincent-Jones
  • Start date
D

David Vincent-Jones

I am trying to obtain the value of the first positive in a column to place
in a summary location.
The column starts with negative values.

I would appreciate help with this question

David
 
P

Pete_UK

Are the values in sequence? If so, this array* formula will give you
the lowest value above zero in the range A1 to A11 (adjust to suit your
range):

=MIN(IF(A1:A11>0,A1:A11))

* As this is an array formula, then once you have typed it in (or
subsequently edit it), you must use CTRL-SHIFT-ENTER instead of just
ENTER. If you do this correctly, then Excel will wrap curly braces { }
around the formula when viewed in the formula bar - you should not type
these yourself.

Hope this helps.

Pete
 
B

Bob Phillips

If they are not in order

=INDEX(A1:A11,MIN(IF(A1:A11>0,ROW(A1:A11))))

again array entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

David Vincent-Jones

Thanks Guys;

Had problems with Pete's proposal but Bob your solution worked just fine ...

I always have problems with Array formulas ...

David
 
D

Dave Peterson

One more:

=INDEX(A1:A25,MATCH(1,((A1:A25>0)*(ISNUMBER(A1:A25))),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.)

Adjust the range to match--but you can't use the whole column.
 
Top