Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
in range a2:a3500 how to find the row number of first cell which its content
value is less than 0.0 and last value which is less than 0.0? They can be set in B2 and B3. Thanks for help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
Hi,
To find the first value less than zero use this array formula =INDEX(A1:A1000,MATCH(TRUE,A1:A1000<0,0),1) To find the last negative number use this array formula =LOOKUP(2,1/(A1:A1000<0),A1:A1000) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Darius" wrote: in range a2:a3500 how to find the row number of first cell which its content value is less than 0.0 and last value which is less than 0.0? They can be set in B2 and B3. Thanks for help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
Thanks but the first eqiuation bounce me back with #N/A and the second
equation give me the last occurance of value less than zero but not the row number which it is located. "Mike H" wrote: Hi, To find the first value less than zero use this array formula =INDEX(A1:A1000,MATCH(TRUE,A1:A1000<0,0),1) To find the last negative number use this array formula =LOOKUP(2,1/(A1:A1000<0),A1:A1000) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Darius" wrote: in range a2:a3500 how to find the row number of first cell which its content value is less than 0.0 and last value which is less than 0.0? They can be set in B2 and B3. Thanks for help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
ok both formula works and give some numbers but not the right one. I am using
this: =INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0),1) Ctrl+Enter+Shift and: =LOOKUP(2,1/(E2:E3286<0),E2:E3286) Ctrl+Enter+Shift beyond wrng data still have problem with row number of the values these two equation pick "Mike H" wrote: Hi, To find the first value less than zero use this array formula =INDEX(A1:A1000,MATCH(TRUE,A1:A1000<0,0),1) To find the last negative number use this array formula =LOOKUP(2,1/(A1:A1000<0),A1:A1000) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Darius" wrote: in range a2:a3500 how to find the row number of first cell which its content value is less than 0.0 and last value which is less than 0.0? They can be set in B2 and B3. Thanks for help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
Hello Darius,
Array-enter =1+MATCH(TRUE,A2:A3500<0,0) resp. =LOOKUP(2,1/(A2:A3500<0),ROW(A2:A3500)) Regards, Bernd |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
Thanks the location works fine based on your equation but the below formula
does not find the first value in the range which is less than 0.0?/ =INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0),1) "Bernd P" wrote: Hello Darius, Array-enter =1+MATCH(TRUE,A2:A3500<0,0) resp. =LOOKUP(2,1/(A2:A3500<0),ROW(A2:A3500)) Regards, Bernd |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
Hello Darius,
That's because one param is missing for MATCH. Array-enter: =INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0,0),1) Regards, Bernd |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
first and last occurance
Perfect thanks
"Bernd P" wrote: Hello Darius, That's because one param is missing for MATCH. Array-enter: =INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0,0),1) Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
occurance in each year | Excel Worksheet Functions | |||
occurance, pivot | Excel Discussion (Misc queries) | |||
Find next occurance | Excel Discussion (Misc queries) | |||
Occurance Counting | Excel Worksheet Functions | |||
frequency for each occurance | Excel Worksheet Functions |