Are you testing for these two conditions?
a.. If array is empty, SMALL returns the #NUM! error value.
a.. If k ? 0 or if k exceeds the number of data points, SMALL returns the
#NUM! error value.

What's the thinking here?

--
Regards,
Tom Ogilvy



"Don Guillett" wrote in message
...
see if this idea helps.

=IF(ISERR(SMALL($A$12:$X$12,2)),"",SMALL($A$12:$X$ 12,2))

--
Don Guillett
SalesAid Software

"Mike7" wrote in
message ...

Hi.
I need help to automate this peace of work with VBA code. Hope someone
will help me.
Links to screenshot if it not appear
http://img15.imgspot.com/u/05/353/16...1135113951.gif
mirror
http://img430.imageshack.us/img430/9...itled215ck.gif

The source range is B2:K46 (45 rows) and the range of second block
where the results must appear is M2:V46 (already with results on
picture).
Generally I need to find 5 smallest numbers in every row and get them
as value 1 in matching rows of second block. Non smallest numbers -
value 0.
But it happens not always when the number of smallest numbers is 5.
Bellow I described criteria for all possible cases. For better
understanding I marked in red color the smallest numbers which are
valid and will get value 1 (I will call them primary smallest numbers),
and in blue color - numbers which are not valid because together with
primary smallest numbers number of them reaches over 5 (I will call
them secondary smallest numbers). They will get value 0.
*Case 1* . An ideal case - 5 smallest numbers.
*Case 2* . In this case we have four primary smallest numbers 1,2,2,3.
Can't add one more because the next in order are two (or more) the same
numbers (4 and 4)
*Case 3* . Three primary smallest numbers 3,4,4. Can't add two more
because the next three (or more) are the same (6,6,6).
*Case 4* . Two primary smallest numbers 1,2. Can't add three more
because the
next four (or more) are the same (3,3,3,3).
*Case 5* . The rule changes here. Only one smallest number (3) and five
next in order numbers (4,4,4,4,4). They all six will get value 1.
*Case 6* . Only one smallest number (6) and six next in order numbers
(7,7,7,7,7,7). All seven will get value 1.
*Cases 7,8,9* . The rule turns back. If the smallest number is unique
and more than six next in order numbers are the same, then only that
one smallest will get value 1.
*Cases 10,11,12,13,14* . More than five the same smallest numbers. All
will get value 1.

Thanks.


+-------------------------------------------------------------------+
|Filename: table1.gif |
|Download: http://www.excelforum.com/attachment.php?postid=4142 |
+-------------------------------------------------------------------+

--
Mike7
------------------------------------------------------------------------
Mike7's Profile:
http://www.excelforum.com/member.php...o&userid=29809
View this thread:
http://www.excelforum.com/showthread...hreadid=495177