Position of first number in array greater than a given number
 

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.
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.

eg:

Array: 6, 9, 3, 4, 2, 8, 8, 2, 7, 5, 6, 1, 4, 3

Adjacent cell contains 7 (first query)

Another adjacent cell contains 5 (Second query)

The answers I am trying to write a formula to obtain are

Query one answer is 2, ie position 2 in the array is the first entry higher
than 7.

Query two answer is 11, ie the 11th entry in the array is the last one
greater than 5.

I suspect the two formulas required are similar uses of multiple referencing
functions.

What my sheet goes onto use from the above is simply "Is the last occurrence
greater than 5 after the first occurance of greater than 7?" True or False
would suffice if it is an easier formula to put together, but I suspect it is
simpler to do it in stages with the two seperate queries and compare the
answers.

Any pointers or formulas to implement the above would be very gratefully
received , I have mused over it for weeks now and it is certainly beyond me.

Thanks

Krapdad

(yes, krap at parenting and also krap at Excel formulas!)

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

Position of first number in array greater than a given number
 

Ron,

Thank you so much for these, both formulas worked immediately just as I
wanted them too.

I had stumbled upon "array entered" formulas in my research but failed to
understand them to the required level, I will need to read up and also to try
and deconstruct the other formula you have provided as I like to understand
all the workings of my sheets. Any brief walkthrough of the formulas you have
provided would also be greatly appreciated but not too worry if you don't
have time.

I am most appreciative of you taking the time to use your obviously in depth
knowledge to resolve my query.

Regards

Krapdad



"Ron Rosenfeld" wrote:

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

Position of first number in array greater than a given number
 

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

In versions prior to Excel 2007 you'd have to use a smaller range for:

($A:$AB2)

The "entire column array rule" applies.

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
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

Position of first number in array greater than a given number
 

On Fri, 3 Apr 2009 10:40:19 -0400, "T. Valko" wrote:

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

In versions prior to Excel 2007 you'd have to use a smaller range for:

($A:$AB2)

The "entire column array rule" applies.

--
Biff
Microsoft Excel MVP

That's interesting. I wasn't sure if it applied there, too.
--ron