It did not even occur to me to put the reference cells in parentheses, which
absolutely makes sense. Thanks to everyone for all of your help.

"Rick Rothstein" wrote:

To expand on OssieMac's response... you can use single cell references as well)

=SMALL((A1,A4,A7:A11),2)

The key is to place the list of cell references in parentheses.

--
Rick (MVP - Excel)


"OssieMac" wrote in message ...
Hi again,

I missed the part ablut the non consecutive cells until I saw the post by
JLGWhiz. However, the following also works:

In the worksheet:
=SMALL((A1:D1,F1:G1,I1:J1,L1,N1:O1),2)

In VBA:
MsgBox WorksheetFunction.Small(Range("A1:D1,F1:G1,I1:J1,L 1,N1:O1"), 2)


--
Regards,

OssieMac


"OssieMac" wrote:

Hi,

My testing indicates that Small does work.

On the worksheet: =SMALL(A1:O1,2)

In VBA: MsgBox WorksheetFunction.Small(Range("A1:O1"), 2)

Your quote: "I tried the SMALL function, but it only allows arrays". A range
is actually an array.

--
Regards,

OssieMac


"J_Squared" wrote:

I need to find the second smallest number in a set of non-consecutive cells,
where all the cells are in the same row. I tried the SMALL function, but it
only allows arrays as inputs. I know I can use MIN to find the smallest, but
I am stumped on how to ge the second smallest. Any suggestions would be
greatly appreciated.