"fzl2007" wrote:
On Feb 21, 2:52 pm, "joeu2004" wrote:
For the data in A1:A6, enter the following __array_formula__
(press ctrl+shift+Enter instead of Enter):
=6-MATCH(2,1/(SIGN(A6)<SIGN(A1:A5)))
6 can be replaced by COUNT(A1:A6).
[....]
I just ran the array formular on this list, =6-MATCH(2,1/
(SIGN(A6)<SIGN(A1:A5))), the solution should be 2 but it
gave a 1
6.4
11.2
2.5
7.3
6.1
-5.5
0.2
5.8

There operative words were "for the data in A1:A6". Since your new example
is 8 cells, presumably in A1:A8, the formula needs to be adapted
accordingly, to wit:

=8-MATCH(2,1/(SIGN(A8)<SIGN(A1:A7)))


"fzl2007" wrote:
Also, how do I change the function so that it will
catch the column rows as it changes? The column data
(range) is dynamic.

The simplest way to do that is to ensure that there is always a cell above
and below (empty or containing text), and you use Insert and Delete to add
and remove data. Then the dynamic formula can be:

=COUNT($A$1:$A$10)
-MATCH(2,1/(SIGN(OFFSET($A$10,-1,0))<SIGN(OFFSET($A$1,1,0,COUNT($A$1:$A$10)))))

That assumes that A1 and A10 are the rows above and below 8 cells of data.

Caveat: OFFSET is a volatile function. So that formula will be
recalculated every time any cell in any worksheet in the workbook is edited.
If you have a lot of such formulas, that can degrade performance
significantly.