Thanks Bernard, I'll give it a whirl...

"Bernard Liengme" wrote:

Can be done with VBA
If you are not too familiar with VBA see David McRitchie's site on "getting
started" with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Copy this to a general macro sheet and on the worksheet use in as in
=Nextsmall(A1:A20)

Function nextsmall(rng)
Small = WorksheetFunction.Max(rng)
For Each num In rng
If num < 0 And num < Small Then
Small = num
End If
Next
Debug.Print "Small " & Small
mytest = WorksheetFunction.Max(rng)
For Each num In rng
If IsNumeric(num) And num < 0 And num < Small Then
If num < mytest Then
mytest = num
End If
End If
Next
nextsmall = mytest
End Function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rachel7" wrote in message
...
Thanks Bernard, this formula only works when all values in the range are
different. What about duplicate values?

"Bernard Liengme" wrote:

=SMALL(IF(A1:A10<0,A1:A10),2)
array formula so enter with Shift+Ctrl+Enter
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Rachel7" wrote in message
...
I need to return the 2nd smallest number in a range of cells, but the
range
contains empty cells, multiple values & zero values. I need to ignore
the
empty cells, multiple values & zero values - can this be done?