=SMALL(A1:A10;6) returns #NUM error

[CWatters]

I'm trying to use

=SMALL(A1:A10;6)

to find the 6th smallest number in A1:A10 but it returns a #NUM error if
there are only 5 fields in the array containing numbers. It doesn't seem to
treat a blank cell as the number zero. Is there a way to supress this
without filling the array with zeros?

Should this work..?

IF(SMALL(A1:A10;6)=#NUM!; 0; SMALL(A1:A10;6))

 

[Jerry W. Lewis]

Try

=SMALL(IF(ISNUMBER(A1:A10);A1:A10,0);6)

array intered (Ctrl-Shift-Enter)

Jerry

 

[Ron Rosenfeld]

I'm trying to use

=SMALL(A1:A10;6)

to find the 6th smallest number in A1:A10 but it returns a #NUM error if
there are only 5 fields in the array containing numbers. It doesn't seem to
treat a blank cell as the number zero. Is there a way to supress this
without filling the array with zeros?

Should this work..?

IF(SMALL(A1:A10;6)=#NUM!; 0; SMALL(A1:A10;6))

Some suggestions depending on what you want for output:

=SMALL(A1:A10,MIN(COUNT(A1:A10),6))

will give #NUM error if there are zero entries.

Or try:

=IF(COUNT(A1:A10)<6,0,SMALL(A1:A10,6))


--ron