Finding 2nd smallest number in range

  • Thread starter Thread starter Rachel7
  • Start date Start date
R

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?
 
=SMALL(IF(A1:A10<>0,A1:A10),2)
array formula so enter with Shift+Ctrl+Enter
best wishes
 
Did you try this : =SMALL(A1:A100,2) this formula ignores text, blank cells and
"0"
HTH
John
 
Thanks Bernard, this formula only works when all values in the range are
different. What about duplicate values?
 
Hi John, thanks for the reponse, my problem is with multiple values in a
range of cells, any ideas?
 
Hi Rachel
Sorry don't know it, wait and see from the others, then will both know it.
Sorry
John
 
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
 
Thanks Bernard, I'll give it a whirl...

Bernard Liengme said:
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
Click to expand...
 
Back
Top