Hi!

For some reason I couldn't get your formula to work for me the way you have
it set up.

I couldn't understand (in your formula) what the ROW($1:$14) was used for.

Yeah, that's my fault!

Usually when I reply to a post I test the formulas and then I copy/paste
them into the reply. Most of the time I use different range sizes and cell
references and then I edit them to meet the description of the posted
question. The ROW($1:$14) was the size of my test range and I missed editing
that after I posted my reply.

The corrected formula should be:

=IF(ISERROR(SMALL(IF(Data!E$2:E$500<"",ROW($1:$49 9)),ROW(1:1))),"",INDEX(Data!A$2:A$500,SMALL(IF(Da ta!E$2:E$500<"",ROW($1:$499)),ROW(1:1))))

ROW($1:$499) refers to the number of entries (size of the range) being used.
The actual physical location may be A2:A500 but the actual physical size of
that range is 499 elements. You could also think of it in terms of being the
virtual range. The virtual range will always start with position 1. Say for
example the physical range location is A101:A500. The virtual range would
still be 1:499.

It's that number that is used by the INDEX function to return the desired
value.

I can send you a sample file that demonstrates this, if you'd like.

Biff

"malik641" wrote in
message ...

Hey Biff,
Thanks for the response. For some reason I couldn't get your formula to
work for me the way you have it set up. I ended using an older formula I
had in another worksheet. It is a combination of two formulas.

First (not an array):
=SUMPRODUCT(--(Data!$E$2:$E$500<0),--(Data!$E$2:$E$500<""))

And then I would use the second formula based on the first.
Second (array formula):
{=IF(ROWS(B$32:B32)<=$I$32,INDEX(Data!$A$2:$A$500, SMALL(IF((Data!$E$2:$E$500<0)*(Data!$E$2:$E$500< ""),ROW(Data!$E$2:$E$500)-ROW(Data!$E$2)+1),ROWS(B$32:B32))),"")}

I couldn't understand (in your formula) what the ROW($1:$14) was used
for. Unless you were displaying the smallest 14 values...??? But oh
well, I got it to work anyway :)

Thanks again for the response Biff


--
malik641


------------------------------------------------------------------------
malik641's Profile:
http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=395491