Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to convert a number to text but I need it to be 5 position | Excel Worksheet Functions | |||
if less then zero put zero if greater then zero state number | Excel Worksheet Functions | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) | |||
count a number that is less than a number and greater than a numbe | Excel Worksheet Functions | |||
find position of a number in a string | Excel Worksheet Functions |