First Positive

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Vincent-Jones - 11 Sep 2006 10:05 GMT

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

Reply to this Message

Pete_UK - 11 Sep 2006 10:34 GMT

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

> I am trying to obtain the value of the first positive in a column to place
> in a summary location.
[quoted text clipped - 3 lines]
>
> David

Reply to this Message

Bob Phillips - 11 Sep 2006 11:31 GMT

If they are not in order

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

again array entered.

Signature

HTH

Bob Phillips

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

> 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
[quoted text clipped - 19 lines]
> >
> > David

Reply to this Message

David Vincent-Jones - 11 Sep 2006 12:43 GMT

Thanks Guys;

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

I always have problems with Array formulas ...

David

> If they are not in order
>
[quoted text clipped - 26 lines]
>> >
>> > David

Reply to this Message

Dave Peterson - 11 Sep 2006 13:28 GMT

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.

> I am trying to obtain the value of the first positive in a column to place
> in a summary location.
[quoted text clipped - 3 lines]
>
> David

Signature

Dave Peterson

Reply to this Message

Rate this thread: