Find specific condition in a range

[TP]

I have a column showing a calculated dollar amount. The amount changes based
on conditions entered elsewhere in the spreadsheet.
The values start at a positive number, decrease (calculated based on a value
entered elsewhere) until they get to a negative number (and keep decreasing
past that point, i.e. lower negative numbers).
How can I return the cell that has the last positive number (or the first
negative number)?

 

[Bernard Liengme]

In A1`:A21 I have 10, 9,8 .... 1, 0, -1 ... -8,-9.-10
This formula =MATCH(MIN(ABS(A1:A21)),A1:A21,0) returns position (11) of the
smallest absolute value (here 0)
And =INDEX(A1:A21,MATCH(MIN(ABS(A1:A21)),A1:A21,0))
this the actual value (zero)
best wishes

 

[T. Valko]

All of these formulas are array formulas**.

For the last positive:

=INDEX(A1:A10,MATCH(TRUE,A1:A10<0,0)-1)

For the first negative:

=INDEX(A1:A10,MATCH(TRUE,A1:A10<0,0))

If the numbers follow a descending sequence:

For the last positive:

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

For the first negative:

=MAX(IF(A1:A10<0,A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.