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( ....)))


--
Biff
Microsoft Excel MVP


"Dan T." wrote in message
...
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))))
--
Dan T.


"Peo Sjoblom" wrote:

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." wrote in message
...
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.
--
Dan T.