Position of first number in array greater than a given number

[Krapdad]

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!)

 

[Ron Rosenfeld]

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:$A>B1,0)

For versions prior to Excel 2007:

=MATCH(TRUE,$A1:$A65535>B1,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:$A>B2),ROW(INDIRECT("1:"&COUNT(A:A))))

(entered normally)
--ron

 

[Krapdad]

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

 

[T. Valko]

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

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

($A:$A>B2)

The "entire column array rule" applies.

 

[Ron Rosenfeld]

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

($A:$A>B2)

The "entire column array rule" applies.

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