trying to use the index function when #N/A can be part of data ran

[Dan T.]

I am using the index function to sample a range of data that will usually
contain several lines of #N/A data. The data is from an external app using
DDE. In using the INDEX function I always get a #N/A result even when the
INDEX is accessing valid rows of the range. If I remove the #N/A from the
data range it works fine. Problem is I need to keep the range inclusive of
any #N/A's it might be retrieving from the DDE. Any sugestions.

 

[Pete_UK]

Perhps you can set up a dummy range elsewhere on the sheet with a
formula like this:

=IF(ISNA(A1),"",A1)

and then copy this down.

Then your INDEX function can access the dummy range (free of #N/A)
instead of the range with A1, but that range can still be updated from
the DDE.

Hope this helps.

Pete

 

[Peo Sjoblom]

INDEX itself would not return the #N/A so I suspect you must use something
else within INDEX that returns the error. e.g.

1
2
3
#N/A
5

in A1:A5

=INDEX(A1:A5,3)

will return 3 whereas

=INDEX(A1:A5,4)

will return #N/A

so you must have some sort of array formula within INDEX

--


Regards,


Peo Sjoblom

 

[Dan T.]

You are right that there is more to the formula. I suspected that it was the
index function causing the problem. It may however be the small function. I
have included the entire formula for you to look at. Thanks for the response
=IF(COUNTIF($AT$4:$BG$50,"="&$A7)<2,"",INDEX($AW$4:$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($1:$48)),ROW($2:$2)))-INDEX($AW$4:$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($1:$48)),ROW($1:$1))))

 

[Spiky]

You are right that there is more to the formula. I suspected that it was the
index function causing the problem. It may however be the small function. I
have included the entire formula for you to look at. Thanks for the response.
=IF(COUNTIF($AT$4:$BG$50,"="&$A7)<2,"",INDEX($AW$4:$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($1:$48)),ROW($2:$2)))-INDEX($AW$4:$AW$52,SMALL(IF($AT$4:$BG$52=$A7,ROW($1:$48)),ROW($1:$1))))

You can test which portion gives the error. Select a section of the
formula in the formula bar and press F9. Then hit Escape to exit the
cell, not Enter or anything else so you don't change the formula.

 

[T. Valko]

sample a range of data that will usually contain several lines of #N/A data

What kind of data is in this range: $AT$4:$BG$52

You've already said it contains #N/A's but what type of data is it? Text,
numeric, both? What's in A7?

This is where you're getting the error:

IF($AT$4:$BG$52=$A7

It breaks down to: IF(#N/A=$A7 and returns #N/A.

You probably need to nest an additional IF. Something like this (depending
on what type of data it is):

IF(ISNUMBER($AT$4:$BG$52),IF($AT$4:$BG$52=$A7,ROW(....)))