On Fri, 3 Apr 2009 05:07:02 -0700, Krapdad
wrote:

Hi all,

I am new to the forum and have searched for the answer to my formula query.
Appologies if I have missed an existing solution, a link would be much
appreciated if it already exists.

My knowledge of referencing and look up formulas is very limited. here is
what I thought was a simple function problem:

In a one column vertical array of numbers I wish to find two seperate things.
Firstly the position in the array (from the top down) of the first
occurrence of a number greater than a number in an adjacent cell.


If your vertical array is in column A, and your adjacent cell is in B1, then
the position of the first occurrence of a number in the array greater than the
number in B1 is given by the **array** formula:

For Excel 2007+ :

=MATCH(TRUE,$A:$AB1,0)

For versions prior to Excel 2007:

=MATCH(TRUE,$A1:$A65535B1,0)


This formula must be **array-entered**:

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.


Secondly, the position in the array (from the top down) of the last
occurrence of a number greater than another number in an adjacent cell.


With the "adjacent cell" being B2:

=LOOKUP(2,1/($A:$AB2),ROW(INDIRECT("1:"&COUNT(A:A))))

(entered normally)
--ron