Search code examples
excelresourcesarray-formulas

Excel out of resources from array formula

I have an excel file where I am trying to link rows with predecessor or successor dependencies (simplified MS project type links)

excel example

The formulas attempt to automatically calculate the successors from the predecessors. The big challenge was with handling the merged cells in column A. The formula is:

{=LARGE(INDIRECT(CONCATENATE("$a$1:$a$",SMALL(IF($C$2:$C$101=INDIRECT(CONCATENATE("a",LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1))),ROW(A$2:A$101)),ROW(A2)-LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)+1))),1)}

As you can see, this calculates the values correctly, but if I add iferror() to handle the error conditions, Excel complains that the function causes it to run out of resources. Is there any way to get around this or to simplify my formula to avoid the issue?

Solution

  • It works if we replace all the INDIRECT(CONCATENATE()) references with the non volatile INDEX:

    =IFERROR(LARGE($A$1:INDEX(A:A,SMALL(IF($C$2:$C$101=INDEX(A:A,LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)),ROW(A$2:A$101)),ROW(A2)-LARGE(IF(ISNUMBER($A$2:$A2),ROW($A$2:$A2)),1)+1)),1),"")

    ![enter image description here