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?

=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?

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?

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?

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?

Did you try this : =SMALL(A1:A100,2) this formula ignores text, blank cells and
"0"
HTH
John
"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?

OOPS, it does't ignore "0" sorry
I see you have a response to your problem.
Regards
John
"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?

Hi John, thanks for the reponse, my problem is with multiple values in a
range of cells, any ideas?

"John" wrote:

OOPS, it does't ignore "0" sorry
I see you have a response to your problem.
Regards
John
"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?

Hi Rachel
Sorry don't know it, wait and see from the others, then will both know it.
Sorry
John
"Rachel7" wrote in message
...
Hi John, thanks for the reponse, my problem is with multiple values in a
range of cells, any ideas?

"John" wrote:

OOPS, it does't ignore "0" sorry
I see you have a response to your problem.
Regards
John
"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?