Position of first number in array greater than a given number
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
|