| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How fix a too long array function
Hello!
I have a matrix with categories in the columns (from A to AG) and I use string reference (with INDIRECT) to access it in another sheet (same workbook though). It is a quite long array function expression in another sheet: {=IF( ($A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4));SUM( IF( ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X2";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X3";INDIRECT('Matrix references'!$B$20))) * ISBLANK(INDIRECT('Matrix references'!$B$18)) * ISERROR(FIND('TR goals'!$G$2;INDIRECT('Matrix references'!$B$8))) * ($A4-7 < INDIRECT('Matrix references'!$B$9)) * (INDIRECT('Matrix references'!$B$9) <= $A4) * (NOT(ISERROR(FIND('TR goals'!$C$2;INDIRECT('Matrix references'!$B$8)))) + NOT(ISERROR(FIND('TR goals'!$C$1;INDIRECT('Matrix references'!$B$8))))); 1; 0) );NA())} What is does is this: Filter out particular rows and sum the occurances/hits in a cell. If no hit, put "N/A". The problem: I can not extend the function, since Excel says is is too long. How can this be fixed? br |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How fix a too long array function
br,
Move all your INDIRECT functions to other cells. For example, enter this in cell B20 on your current sheet: =INDIRECT('Matrix references'!$B$20) and replace INDIRECT('Matrix references'!$B$20) with B20 in your long formula. Also, you could move this =$A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4) to a cell, say, A5, and use =IF(A5; as the start of your formula. HTH, Bernie MS Excel MVP "KalleH" wrote in message ... Hello! I have a matrix with categories in the columns (from A to AG) and I use string reference (with INDIRECT) to access it in another sheet (same workbook though). It is a quite long array function expression in another sheet: {=IF( ($A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4));SUM( IF( ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X2";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X3";INDIRECT('Matrix references'!$B$20))) * ISBLANK(INDIRECT('Matrix references'!$B$18)) * ISERROR(FIND('TR goals'!$G$2;INDIRECT('Matrix references'!$B$8))) * ($A4-7 < INDIRECT('Matrix references'!$B$9)) * (INDIRECT('Matrix references'!$B$9) <= $A4) * (NOT(ISERROR(FIND('TR goals'!$C$2;INDIRECT('Matrix references'!$B$8)))) + NOT(ISERROR(FIND('TR goals'!$C$1;INDIRECT('Matrix references'!$B$8))))); 1; 0) );NA())} What is does is this: Filter out particular rows and sum the occurances/hits in a cell. If no hit, put "N/A". The problem: I can not extend the function, since Excel says is is too long. How can this be fixed? br |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How fix a too long array function
Ok, I will try that.
Do you know if it is possible to include the result from a cell with an expression like ={ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20)))} in another cell's array function expression. That too would minimize the expression length I hope. br KalleH "Bernie Deitrick" wrote: br, Move all your INDIRECT functions to other cells. For example, enter this in cell B20 on your current sheet: =INDIRECT('Matrix references'!$B$20) and replace INDIRECT('Matrix references'!$B$20) with B20 in your long formula. Also, you could move this =$A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4) to a cell, say, A5, and use =IF(A5; as the start of your formula. HTH, Bernie MS Excel MVP "KalleH" wrote in message ... Hello! I have a matrix with categories in the columns (from A to AG) and I use string reference (with INDIRECT) to access it in another sheet (same workbook though). It is a quite long array function expression in another sheet: {=IF( ($A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4));SUM( IF( ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X2";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X3";INDIRECT('Matrix references'!$B$20))) * ISBLANK(INDIRECT('Matrix references'!$B$18)) * ISERROR(FIND('TR goals'!$G$2;INDIRECT('Matrix references'!$B$8))) * ($A4-7 < INDIRECT('Matrix references'!$B$9)) * (INDIRECT('Matrix references'!$B$9) <= $A4) * (NOT(ISERROR(FIND('TR goals'!$C$2;INDIRECT('Matrix references'!$B$8)))) + NOT(ISERROR(FIND('TR goals'!$C$1;INDIRECT('Matrix references'!$B$8))))); 1; 0) );NA())} What is does is this: Filter out particular rows and sum the occurances/hits in a cell. If no hit, put "N/A". The problem: I can not extend the function, since Excel says is is too long. How can this be fixed? br |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How fix a too long array function
Hello!
I tried your solution, it did not work. No Excel error resulted though, but incorrect result. I think the nested array function got different (using a reference to a reference). Br kalleH "Bernie Deitrick" wrote: br, Move all your INDIRECT functions to other cells. For example, enter this in cell B20 on your current sheet: =INDIRECT('Matrix references'!$B$20) and replace INDIRECT('Matrix references'!$B$20) with B20 in your long formula. Also, you could move this =$A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4) to a cell, say, A5, and use =IF(A5; as the start of your formula. HTH, Bernie MS Excel MVP "KalleH" wrote in message ... Hello! I have a matrix with categories in the columns (from A to AG) and I use string reference (with INDIRECT) to access it in another sheet (same workbook though). It is a quite long array function expression in another sheet: {=IF( ($A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4));SUM( IF( ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X2";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X3";INDIRECT('Matrix references'!$B$20))) * ISBLANK(INDIRECT('Matrix references'!$B$18)) * ISERROR(FIND('TR goals'!$G$2;INDIRECT('Matrix references'!$B$8))) * ($A4-7 < INDIRECT('Matrix references'!$B$9)) * (INDIRECT('Matrix references'!$B$9) <= $A4) * (NOT(ISERROR(FIND('TR goals'!$C$2;INDIRECT('Matrix references'!$B$8)))) + NOT(ISERROR(FIND('TR goals'!$C$1;INDIRECT('Matrix references'!$B$8))))); 1; 0) );NA())} What is does is this: Filter out particular rows and sum the occurances/hits in a cell. If no hit, put "N/A". The problem: I can not extend the function, since Excel says is is too long. How can this be fixed? br |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How fix a too long array function
br/KalleH,
You could try putting this into cell B20: =INDIRECT('Matrix references'!$B$20) and using INDIRECT(B20) on place of INDIRECT('Matrix references'!$B$20) That would remove the sheet references at least, making the formula shorter. The other approach would be to re-write your formula.... if you describe what your formula is doing, there may be a better formula approach.... HTH, Bernie MS Excel MVP "KalleH" wrote in message ... Ok, I will try that. Do you know if it is possible to include the result from a cell with an expression like ={ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20)))} in another cell's array function expression. That too would minimize the expression length I hope. br KalleH "Bernie Deitrick" wrote: br, Move all your INDIRECT functions to other cells. For example, enter this in cell B20 on your current sheet: =INDIRECT('Matrix references'!$B$20) and replace INDIRECT('Matrix references'!$B$20) with B20 in your long formula. Also, you could move this =$A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4) to a cell, say, A5, and use =IF(A5; as the start of your formula. HTH, Bernie MS Excel MVP "KalleH" wrote in message ... Hello! I have a matrix with categories in the columns (from A to AG) and I use string reference (with INDIRECT) to access it in another sheet (same workbook though). It is a quite long array function expression in another sheet: {=IF( ($A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4));SUM( IF( ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X2";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X3";INDIRECT('Matrix references'!$B$20))) * ISBLANK(INDIRECT('Matrix references'!$B$18)) * ISERROR(FIND('TR goals'!$G$2;INDIRECT('Matrix references'!$B$8))) * ($A4-7 < INDIRECT('Matrix references'!$B$9)) * (INDIRECT('Matrix references'!$B$9) <= $A4) * (NOT(ISERROR(FIND('TR goals'!$C$2;INDIRECT('Matrix references'!$B$8)))) + NOT(ISERROR(FIND('TR goals'!$C$1;INDIRECT('Matrix references'!$B$8))))); 1; 0) );NA())} What is does is this: Filter out particular rows and sum the occurances/hits in a cell. If no hit, put "N/A". The problem: I can not extend the function, since Excel says is is too long. How can this be fixed? br |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How fix a too long array function
I'm sorry - one too many INDIRECTS - use
='Matrix references'!$B$20 in B20, and INDIRECT(B20) to remove the sheet references... HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... br/KalleH, You could try putting this into cell B20: =INDIRECT('Matrix references'!$B$20) and using INDIRECT(B20) on place of INDIRECT('Matrix references'!$B$20) That would remove the sheet references at least, making the formula shorter. The other approach would be to re-write your formula.... if you describe what your formula is doing, there may be a better formula approach.... HTH, Bernie MS Excel MVP "KalleH" wrote in message ... Ok, I will try that. Do you know if it is possible to include the result from a cell with an expression like ={ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20)))} in another cell's array function expression. That too would minimize the expression length I hope. br KalleH "Bernie Deitrick" wrote: br, Move all your INDIRECT functions to other cells. For example, enter this in cell B20 on your current sheet: =INDIRECT('Matrix references'!$B$20) and replace INDIRECT('Matrix references'!$B$20) with B20 in your long formula. Also, you could move this =$A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4) to a cell, say, A5, and use =IF(A5; as the start of your formula. HTH, Bernie MS Excel MVP "KalleH" wrote in message ... Hello! I have a matrix with categories in the columns (from A to AG) and I use string reference (with INDIRECT) to access it in another sheet (same workbook though). It is a quite long array function expression in another sheet: {=IF( ($A4-7 < TODAY()) * ($A4 < TODAY())+(($A4-7 < TODAY()) * (TODAY() <=$A4));SUM( IF( ISERROR(FIND("X1";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X2";INDIRECT('Matrix references'!$B$20))) * ISERROR(FIND("X3";INDIRECT('Matrix references'!$B$20))) * ISBLANK(INDIRECT('Matrix references'!$B$18)) * ISERROR(FIND('TR goals'!$G$2;INDIRECT('Matrix references'!$B$8))) * ($A4-7 < INDIRECT('Matrix references'!$B$9)) * (INDIRECT('Matrix references'!$B$9) <= $A4) * (NOT(ISERROR(FIND('TR goals'!$C$2;INDIRECT('Matrix references'!$B$8)))) + NOT(ISERROR(FIND('TR goals'!$C$1;INDIRECT('Matrix references'!$B$8))))); 1; 0) );NA())} What is does is this: Filter out particular rows and sum the occurances/hits in a cell. If no hit, put "N/A". The problem: I can not extend the function, since Excel says is is too long. How can this be fixed? br |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HELP! with long function | Excel Worksheet Functions | |||
| Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
| Long Logical function | Excel Worksheet Functions | |||
| Does Excel 2003 have a function to calculate how long money will l | Excel Worksheet Functions | |||
| Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Linear Mode
