How fix a too long array function

  • Thread starter Thread starter KalleH
  • Start date Start date
K

KalleH

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top