Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first value that is less than zero
I have a list of values in column b. I want to find the first time that this
value is less than zero. I then want to list the corresponding row in column a. any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first value that is less than zero
Array-enter this expression in say C2,
ie confirm it by pressing CTRL+SHIFT+ENTER: =INDEX(A2:A5,MATCH(MIN(IF(B2:B5<0,B2:B5)),IF(B2:B5 <0,B2:B5),0)) Adapt the ranges to suit your actual data extents in cols A and B Success? Punch it here, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "russhess8" wrote: I have a list of values in column b. I want to find the first time that this value is less than zero. I then want to list the corresponding row in column a. any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first value that is less than zero
Try the below.Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(A1:A100,MIN(IF(B1:B100<0,ROW(B1:B100)))) If this post helps click Yes --------------- Jacob Skaria "russhess8" wrote: I have a list of values in column b. I want to find the first time that this value is less than zero. I then want to list the corresponding row in column a. any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first value that is less than zero
Try this...
Assume your data is in the range B2:B20 Array entered** : =MATCH(TRUE,B2:B20<0,0)+ROW(B2)-1 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "russhess8" wrote in message ... I have a list of values in column b. I want to find the first time that this value is less than zero. I then want to list the corresponding row in column a. any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first value that is less than zero
Hmmm...
I then want to list the corresponding row in column a. I interpret that to mean you want the formula entered in some cell in column A that returns the *row number* of the cell in column B that meets the condition. From reading the other replies I'm the only one that interprets it in this way. If you do want to return the corresponding value from column A where column B is <0 then try this array formula** : =INDEX(A2:A20,MATCH(TRUE,B2:B20<0,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Assume your data is in the range B2:B20 Array entered** : =MATCH(TRUE,B2:B20<0,0)+ROW(B2)-1 ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "russhess8" wrote in message ... I have a list of values in column b. I want to find the first time that this value is less than zero. I then want to list the corresponding row in column a. any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the value of x | Excel Worksheet Functions | |||
Finding The MAX Value | Excel Worksheet Functions | |||
Finding Max or Min Value | Excel Discussion (Misc queries) | |||
Finding Row | Excel Discussion (Misc queries) | |||
finding value | Excel Discussion (Misc queries) |