#NA's resistant to defeat !

What do you want to do with your data

Originally Posted by sandy666

last position is IFERROR(5,"") and result should be 5 not #N/A, right? or if error than: ""

Not the problem at all. It returns the numbers as expected. It returns #NA's as expected. It's the resistance of #NA suppression that has me stymied. I even stumbled upon a way to take care of that. I just don't understand how it works when nothing else did..........and why it is such a challenge in the first place.

Edit: BTW this is not a pressing problem. This is about furthering my Excel education.

Hm.. nice question but me too puzzled by the way the Iferror() reacts

Originally Posted by FlameRetired

Yeah! Me, too. It's never made any sense to me.

I posted this in Call In The Cavalry to get everyones view/suggestion

As far as I can see your first IFERROR formula can only return two results but you array entered it into more rows. The N/A isn't returned by the formula itself, it's Excel indicating there is nothing to return in the additional rows.

Originally Posted by FlameRetired

Are you saying that because of the nature of this array formula...... needing to be committed CSE to the entire range at once

If I am not wrong, I read Rory statement as

"Passing invalid references to Index() function is overriding Iferror() function to let you know that the passed range is not valid"

In A1: 0
functions as expected:

Formula:

Please Login or Register  to view this content.

Hi.

For a start, can I ask why you're passing a ROW/INDIRECT construction as SMALL's k parameter here?

Have you followed through your formula using the Evaluate Formula tool? If so, you will notice that this construction does not resolve to an array. For example, for your formula in E3:

=IFERROR(INDEX($A$2:$A$24,SMALL(INDEX(($A$2:$A$24=0)*25^25+ROW($A$2:$A$24)-MIN(ROW($A$2:$A$24))+1,0),ROW(INDIRECT((RIGHT(E$2,1)-1)*2+1&":"&(RIGHT(E$2,1)-1)*2+2)))),"")

this part:

ROW(INDIRECT((RIGHT(E$2,1)-1)*2+1&":"&(RIGHT(E$2,1)-1)*2+2))

will resolve as far as:

ROW($1:$2)

though, in the next step, this will not resolve to the desired:

{1;2}

but rather to simply the first element within this array, i.e. 1.

The reason being that, in order to pass an array as one of INDEX's parameters, a little extra coercion is required, e.g.:

=IFERROR(INDEX($A$2:$A$24,N(IF(1,SMALL(INDEX(($A$2:$A$24=0)*25^25+ROW($A$2:$A$24)-MIN(ROW($A$2:$A$24))+1,0),ROW(INDIRECT((RIGHT(E$2,1)-1)*2+1&":"&(RIGHT(E$2,1)-1)*2+2)))))),"")

and now the ROW/INDIRECT construction would resolve to the desired array to pass to INDEX, i.e. {1,2}.

See here for more:

http://excelxor.com/2014/09/05/index...ray-of-values/

Secondly, your case just reminds me why I avoid multi-cell array formulas like the plague: far less understood than most people imagine, and not at all simply the equivalent of a single-cell array formula copied down the same number of rows. romperstomper's analysis is spot-on.

Regards

Originally Posted by FlameRetired

understood the wrong INDEX function to be the issue?

I am not saying Index is the problem my point is

Passing invalid references

Originally Posted by :) Sixthsense :)

I am not saying Index is the problem my point is

Passing invalid references

Sorry I was writing while you posted. I didn't understand quite what you meant earlier, but I think it's beginning to dawn on me in "Flame-speak" terms.

Originally Posted by FlameRetired

OK. I've F9ed and Fx'd this thing to death. I may have figured something out.

When I Fx any of the cells that contain NA the last step is IFERROR(5,""). It never occurred to me that could be the problem......5 is not an error but the formula cannot find an available indexed 5 left in the array.....so it returns a different kind of NA.......saying "I can't find an error, but I can't find a 5 either.....so....I'll do this....". Come to think of it that might be what sandy666 was trying to get across earlier.

No. That's not at all what is occurring. Re-read romperstomper's post and perhaps do some further research on multi-cell array formulas. Or, better still, don't use them in the first place!

Originally Posted by XOR LX

No. That's not at all what is occurring. Re-read romperstomper's post and perhaps do some further research on multi-cell array formulas. Or, better still, don't use them in the first place!

Yes. You've raised the bar for me on this and I thank you for it. I have read some of those articles N(IF(1, array to coerce) among others. I've even tried it a few times to experiment. This the first time I've seen where I could have put it to use in earnest.

Yes I did notice the resolve to ROW($1:$2), but I did not notice it not resolving to {1;2}. I have now. Thanks for pointing that out.

I will go back and look at romperstomper's post. I'm afraid it blew right over my head when I first read it.

And avoiding multi-cell array formulas altogether.......I see your point.

RE: For a start, can I ask why you're passing a ROW/INDIRECT construction as SMALL's k parameter here?

It was for nothing more than my own "off-line" education. I wanted to see if I could........I'm a bit INDIRECT challenged, and as the 'assignment' was to return two groups of two I thought I would try using the column header to start my SMALL k parameter. Please understand when I did all this I had absolutely no idea that I would be posting this mess. The annoying NA really got my interest when I stumbled upon something that worked and I couldn't understand that either......although I think I can now.


@snb You're formula intrigues me. I will try it and attempt to "unpack" it. Thank you.


And thank you everyone who contributed to this thread. It's been enlightening.

@FlameRetired

Just to say that I think it's admirable what you're doing here.

In my opinion there is no better way to learn advanced Excel than to pose oneself such questions, investigate them further, analyse any peculiarities/unexpected results and then attempt to push the boundaries even further with regards to one's understanding of the functions/constructions involved.

And even better if you share this knowledge on a public forum, to which all may contribute and from which all may learn.

So keep up the good work and maintain your philosophy of enquiry. There should be more of it about!

Cheers

A simpler example to demonstrate: select 4 cells in a column and type ={1;2} then Ctrl+Shift+Enter. The formula itself can't return an error but you're returning an array of two elements to 4 cells so Excel simply fills the rest with #N/A.