| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array subtitute
Is there a way that following could be used without an array
=IF($C6="","",IF(ISERROR(INDEX(Matrix!G$5:G$475,SM ALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)),COUNTI F('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6)))),INDEX(Matrix!G$5:G$475,SMALL(IF(Ma trix!$A$5:$A$475=$C6,ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6))),INDEX(Matrix!G$5:G$475,SMALL(IF(Mat rix!$C$5:$C$475=$C6,ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6))))) i have to get result for the same value twice or thrice, n array is taking too much time bcz i have almost 150 columns with 450 rows any help plz regards |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array subtitute
Not adapted to your condition, but here is a non-array approach
In C1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In D1: =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) copy C1:D1 down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Salman" wrote in message ... Is there a way that following could be used without an array =IF($C6="","",IF(ISERROR(INDEX(Matrix!G$5:G$475,SM ALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)),COUNTI F('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6)))),INDEX(Matrix!G$5:G$475,SMALL(IF(Ma trix!$A$5:$A$475=$C6,ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6))),INDEX(Matrix!G$5:G$475,SMALL(IF(Mat rix!$C$5:$C$475=$C6,ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6))))) i have to get result for the same value twice or thrice, n array is taking too much time bcz i have almost 150 columns with 450 rows any help plz regards |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array subtitute
"Bob Phillips" wrote...
Not adapted to your condition, but here is a non-array approach In C1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In D1: =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) copy C1:D1 down .... You col C formula produces the row number on the first instance of the current row's col A value in col A from row 1 to the current row when the col A value isn't ""; otherwise, it returns "". It'd be more efficient to use C1: =IF(COUNT(1/(MATCH(A1,A$1:A1,0)=ROW())),ROW(),"") since match could return upon finding the first instance rather than process all of col C from row 1 to the current row. Your col D formula doesn't do what the OP's formula does. "Salman" wrote... Is there a way that following could be used without an array =IF($C6="","",IF(ISERROR(INDEX(Matrix!G$5:G$47 5, SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)), COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6)))), INDEX(Matrix!G$5:G$475,SMALL(IF(Matrix!$A$5:$A$4 75=$C6, ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6, 'Matrix REPORT'!$C6))),INDEX(Matrix!G$5:G$475, SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)), COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6))))) .... This formula appears to be entered into some cell in row 6 in the 'Matrix REPORT' worksheet. If so, and if the only error being trapped is #NUM! when the SMALL call's 1st arg contains fewer number values than its 2nd arg, then it could be rewritten as =IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL( IF(IF(COUNTIF(Matrix!$C$5:$C$475,$C6)<COUNTIF($C$6 :$C6,$C6), Matrix!$A$5:$A$475,Matrix!$C$5:$C$475)=$C6,ROW($1: $471)), COUNTIF($C$6:$C6,$C6)))) To me this begs the question whether Matrix!A5:A475 = C6 whenever Matrix!C5:C475 = C6. If it does, simplify further to =IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL( IF(Matrix!$A$5:$A$475=$C6,ROW($1:$471)), COUNTIF($C$6:$C6,$C6)))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array subtitute
Thanks, it would be helpful i think so but it seems a bit complicated to me,
let me work on it, if i have any problem then i will come back and give you a rescue call, thanks alot. "Harlan Grove" wrote: "Bob Phillips" wrote... Not adapted to your condition, but here is a non-array approach In C1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In D1: =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) copy C1:D1 down .... You col C formula produces the row number on the first instance of the current row's col A value in col A from row 1 to the current row when the col A value isn't ""; otherwise, it returns "". It'd be more efficient to use C1: =IF(COUNT(1/(MATCH(A1,A$1:A1,0)=ROW())),ROW(),"") since match could return upon finding the first instance rather than process all of col C from row 1 to the current row. Your col D formula doesn't do what the OP's formula does. "Salman" wrote... Is there a way that following could be used without an array =IF($C6="","",IF(ISERROR(INDEX(Matrix!G$5:G$47 5, SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)), COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6)))), INDEX(Matrix!G$5:G$475,SMALL(IF(Matrix!$A$5:$A$4 75=$C6, ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6, 'Matrix REPORT'!$C6))),INDEX(Matrix!G$5:G$475, SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)), COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6))))) .... This formula appears to be entered into some cell in row 6 in the 'Matrix REPORT' worksheet. If so, and if the only error being trapped is #NUM! when the SMALL call's 1st arg contains fewer number values than its 2nd arg, then it could be rewritten as =IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL( IF(IF(COUNTIF(Matrix!$C$5:$C$475,$C6)<COUNTIF($C$6 :$C6,$C6), Matrix!$A$5:$A$475,Matrix!$C$5:$C$475)=$C6,ROW($1: $471)), COUNTIF($C$6:$C6,$C6)))) To me this begs the question whether Matrix!A5:A475 = C6 whenever Matrix!C5:C475 = C6. If it does, simplify further to =IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL( IF(Matrix!$A$5:$A$475=$C6,ROW($1:$471)), COUNTIF($C$6:$C6,$C6)))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Array subtitute
please look at the following and advise please that y i am not getting the
right values, this formula provide results but not correct, for first 4 rows answer is correct but after that it is getting the values from next columns which is not refered in this formula, please help =IF(C6="","",IF(ISERROR(SUMPRODUCT((Matrix!$C$6:$C $536='Matrix REPORT'!$C6)*(Matrix!$E$6:$E$536='Matrix REPORT'!$E6)*Matrix!$H$6:$H$536)),SUMIF('SECTOR AVERAGE'!$B$5:$H$715,'Matrix REPORT'!$C6,'SECTOR AVERAGE'!$G$5:$G$715),SUMPRODUCT((Matrix!$C$6:$C$5 36='Matrix REPORT'!$C6)*(Matrix!$E$6:$E$536='Matrix REPORT'!$E6)*Matrix!$H$6:$H$536))) "Harlan Grove" wrote: "Bob Phillips" wrote... Not adapted to your condition, but here is a non-array approach In C1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) In D1: =IF(ISERROR(SMALL(C:C,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(C:C,ROW(A1)),C:C,0))) copy C1:D1 down .... You col C formula produces the row number on the first instance of the current row's col A value in col A from row 1 to the current row when the col A value isn't ""; otherwise, it returns "". It'd be more efficient to use C1: =IF(COUNT(1/(MATCH(A1,A$1:A1,0)=ROW())),ROW(),"") since match could return upon finding the first instance rather than process all of col C from row 1 to the current row. Your col D formula doesn't do what the OP's formula does. "Salman" wrote... Is there a way that following could be used without an array =IF($C6="","",IF(ISERROR(INDEX(Matrix!G$5:G$47 5, SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)), COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6)))), INDEX(Matrix!G$5:G$475,SMALL(IF(Matrix!$A$5:$A$4 75=$C6, ROW($1:$471)),COUNTIF('Matrix REPORT'!$C$6:$C6, 'Matrix REPORT'!$C6))),INDEX(Matrix!G$5:G$475, SMALL(IF(Matrix!$C$5:$C$475=$C6,ROW($1:$471)), COUNTIF('Matrix REPORT'!$C$6:$C6,'Matrix REPORT'!$C6))))) .... This formula appears to be entered into some cell in row 6 in the 'Matrix REPORT' worksheet. If so, and if the only error being trapped is #NUM! when the SMALL call's 1st arg contains fewer number values than its 2nd arg, then it could be rewritten as =IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL( IF(IF(COUNTIF(Matrix!$C$5:$C$475,$C6)<COUNTIF($C$6 :$C6,$C6), Matrix!$A$5:$A$475,Matrix!$C$5:$C$475)=$C6,ROW($1: $471)), COUNTIF($C$6:$C6,$C6)))) To me this begs the question whether Matrix!A5:A475 = C6 whenever Matrix!C5:C475 = C6. If it does, simplify further to =IF($C6="","",INDEX(Matrix!G$5:G$475,SMALL( IF(Matrix!$A$5:$A$475=$C6,ROW($1:$471)), COUNTIF($C$6:$C6,$C6)))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
| Use array to return array of values | Excel Worksheet Functions | |||
| Return Array with Array | Excel Worksheet Functions | |||
| Goal Seek On Members of an Array within Array | Excel Worksheet Functions | |||
Linear Mode
