Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row based on a random value in a column
First, apologies if this has been answered before but I couldn't find
anything close in the forums: My problem is I need to find the first row reference if a value in one of the columns is (unpredictably) less than 0. I have tried a mix of Index, Match and the Excel lookup wizard but to no avail. For example, at its simplest I have 2 columns x and y A B 1 x y 2 1 13 3 2 6 4 3 0 5 4 -1 6 5 -12 7 6 4 etc how do I locate the first row when the y column goes negative. I know it is simple if I know beforehand ( in this case it is row cell A5) , but if the Y column varies at random, I get stuck. Any help much appreciated Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row based on a random value in a column
Hi!
One way: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =IF(COUNTIF(B2:B7,"<0"),MATCH(TRUE,B2:B7<0,0)+1,"" ) Biff "bobzebra" wrote in message oups.com... First, apologies if this has been answered before but I couldn't find anything close in the forums: My problem is I need to find the first row reference if a value in one of the columns is (unpredictably) less than 0. I have tried a mix of Index, Match and the Excel lookup wizard but to no avail. For example, at its simplest I have 2 columns x and y A B 1 x y 2 1 13 3 2 6 4 3 0 5 4 -1 6 5 -12 7 6 4 etc how do I locate the first row when the y column goes negative. I know it is simple if I know beforehand ( in this case it is row cell A5) , but if the Y column varies at random, I get stuck. Any help much appreciated Bob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row based on a random value in a column
Since you mentioned A5, are you looking for the return to come from Column
A? If so, try this *array* formula: =INDEX(A1:A7,MATCH(TRUE,B1:B7<0,0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "bobzebra" wrote in message oups.com... First, apologies if this has been answered before but I couldn't find anything close in the forums: My problem is I need to find the first row reference if a value in one of the columns is (unpredictably) less than 0. I have tried a mix of Index, Match and the Excel lookup wizard but to no avail. For example, at its simplest I have 2 columns x and y A B 1 x y 2 1 13 3 2 6 4 3 0 5 4 -1 6 5 -12 7 6 4 etc how do I locate the first row when the y column goes negative. I know it is simple if I know beforehand ( in this case it is row cell A5) , but if the Y column varies at random, I get stuck. Any help much appreciated Bob |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding a row based on a random value in a column
Guys: brilliant many thanks; works a treat. More importantly you have
opened door on Array formulas. I simply hadn't reaslied they existed in Excel. But I suppose without a first example it doesn't naturally occur to you that they may solve your problem. thanks again. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
move contents of column C based on criteria related to column A | Excel Discussion (Misc queries) | |||
"Cumulative" column based on a pivot table sum column? | Excel Worksheet Functions | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
Calculating totals in a column based on a lookup in another column | Excel Worksheet Functions |