find first positive number in column

[Sheila D]

I have a column of figures, maximum of 7 numbers in total and need to find
the first positive number in the column, negatives will always be shown first
and are dependant on other calculations. Some of the last cells may be blank
depending on the periods in the term ie
1. -10
2. -1
3. 10
4. 5
5. 80
6.
7.
Should return 10 as the first positive in the list
Is there a built in fucnction I can use to do this? Many thanks for any help

Sheila D

 

[Dave Peterson]

=INDEX(A1:A10,MATCH(TRUE,A1:A10>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.)

Adjust the range to match--but you can only use the whole column in xl2007.

 

[Gary''s Student]

For column A:

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

must be entered as an array formula with CNTRL-SHFT-ENTER rather than just
the ENTER key.

 

[Don Guillett]

A couple of ways. Both are array formulas that must be entered using
ctrl+shift+enter

=MIN(IF(B2:B22>0,B2:B22))
=INDEX(B2:B10,MATCH(TRUE,B2:B10>0,0))

 

[Sheila D]

Thank you very much

 

[Sheila D]

Hi agfin and thanks for previous help
As a follow on from that once I have found the first positive value I need
to output that and then all following values in sequence ie from
1. -10

2. -1
3. 10
4. 5
5. 80
6.
7.

10 then 5 then 80
Is there a way to specify the next record(s) where value is present.

Sheila

 

[Ron Rosenfeld]

Hi agfin and thanks for previous help
As a follow on from that once I have found the first positive value I need
to output that and then all following values in sequence ie from
1. -10
10 then 5 then 80
Is there a way to specify the next record(s) where value is present.

Sheila

Assuming your data is in A1:A7

Enter this formula in some cell, then fill down seven rows:

=IF(INDEX($A$1:$A$7,ROWS($1:1)-1+MATCH(TRUE,$A$1:$A$7>0,0))>0,
INDEX($A$1:$A$7,ROWS($1:1)-1+MATCH(TRUE,$A$1:$A$7>0,0)),"")

As before, this is an **array** formula to be entered with
<ctrl><shift><enter>.
--ron

 

[Sheila D]

Thank you Ron, I will give that a try

Assuming your data is in A1:A7

Enter this formula in some cell, then fill down seven rows:

=IF(INDEX($A$1:$A$7,ROWS($1:1)-1+MATCH(TRUE,$A$1:$A$7>0,0))>0,
INDEX($A$1:$A$7,ROWS($1:1)-1+MATCH(TRUE,$A$1:$A$7>0,0)),"")

As before, this is an **array** formula to be entered with
<ctrl><shift><enter>.
--ron