I've been working a lot with excel the last couple of days and can't figure out an error i'll get:
Here is what i originally want to do:
=IFERROR(INDIRECT(ADDRESS((IF(Array>0;1;-1)*COLUMN(Array)-31)*51+$CJ3+1;COLUMN(CP3)));1)
Where Array is a Array Formula on its own:
Array =INDIRECT(ADDRESS('Tab1'!$BT3;31;;;"List")&":"&ADDRESS('Tab1'!$BT3;43))
Evaluated everything as Array Formula i get:
{1\1\1\1\1\1\1\1\1}
This is not what i would expect. I would like to only get rid of the Errors and get an array like:
{22\76\1\1\1\1\1\1\1\1}
I decided to step through it step by step with F9 and what I get is a totally fine array resulting from the outer most ÌNDIRECT: {4\18\#VALUE!\#VALUE!\#VALUE!\#VALUE!\#VALUE!\#VALUE!\#VALUE!} (The #VALUE! Errors are meant to be there)
Again evaluating the IFERROR I get only 1s. On top of that, the F9 Functionality doesn't seem to care whether it's an array formula. At least the results stay exactly the same.
So I tried to do it with the Evaluate Formula Functionality in Excel. Funny enough the Functionality shows an error occurring earlier within the Indirect, which at least is way more reasonable.
As a result of the outer most Indirect it only shows #VALUE! Errors. I checked the Address but everything seems to be fine. (Again evaluated with F9 works)
This is where I got a bit desperate. I tried with OFFSET and INDEX instead of INDIRECT but the results are similar:
With OFFSET I do get exactly the same results. F9 works, Evaluate Formula returns an array of Errors.
With INDEX I was probably tilted or had a blackout. I just cant get INDEX as Array Formula working:
=COUNTIF(INDEX($CL:$CL;(IF(Array>0;1;-1)*COLUMN(Array)-31)*51+6+1-1);">0") and
=COUNTIF(INDEX($CL5:$CL7;{6\7});">0")
with CTRL-SHIFT-ENTER result in the inner part of the INDEX not returning an array.
Fair enough: it seems like my Indirect doesn't work. Maybe I do reference some random ...
So i tried: =SUM(1*OFFSET($A$1;{1};{1})) as array formula, but it fails. The same for =SUM(1*OFFSET($A$1;{1\2};1)). Only evaluating the whole thing not as array formula works: =SUM(1*OFFSET($A$1;1;1)).
Screenshot of Tab "List".
Tab "Tab1" is where I'm working in.
Question: Any idea on how to solve the ISERROR formula above?
Why does IFERROR only return 1 for Errors?
And, of course, any help on my workarounds is very appreciated as well.
1 Solution:
Excel Formula: Using INDIRECT to get same row number with named range causes #VALUE! error
Thx to Axel Richter
