Finding 2nd smallest number in range

[Rachel7]

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?

 

[Bernard Liengme]

=SMALL(IF(A1:A10<>0,A1:A10),2)
array formula so enter with Shift+Ctrl+Enter
best wishes

 

[John]

Did you try this : =SMALL(A1:A100,2) this formula ignores text, blank cells and
"0"
HTH
John

 

[Rachel7]

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

 

[John]

OOPS, it does't ignore "0" sorry
I see you have a response to your problem.
Regards
John

 

[Rachel7]

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

 

[John]

Hi Rachel
Sorry don't know it, wait and see from the others, then will both know it.
Sorry
John

 

[Bernard Liengme]

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

 

[Rachel7]

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

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

 

[Gord Dibben]

=IF(COUNTA(A1:A30<2,""),SMALL(A1:A30,2))


Gord Dibben MS Excel MVP