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

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

Sheila D wrote:

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

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

=MIN(IF(B2:B220,B2:B22))
=INDEX(B2:B10,MATCH(TRUE,B2:B100,0))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sheila D" wrote in message
...
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

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.
--
Gary''s Student - gsnu200799

Thank you very much

"Sheila D" wrote:

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

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

"Sheila D" wrote:

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

On Mon, 11 Aug 2008 05:50:00 -0700, Sheila D
wrote:

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

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$70,0))0,
INDEX($A$1:$A$7,ROWS($1:1)-1+MATCH(TRUE,$A$1:$A$70,0)),"")

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

Thank you Ron, I will give that a try

"Ron Rosenfeld" wrote:

On Mon, 11 Aug 2008 05:50:00 -0700, Sheila D
wrote:

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

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$70,0))0,
INDEX($A$1:$A$7,ROWS($1:1)-1+MATCH(TRUE,$A$1:$A$70,0)),"")

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