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

 

[Jared L.]

I need it to complete the equation or a default 0 if there is nothing entered in for the values. In every column there has to be a value entered otherwise the end result is #NUM!




John wrote:

Re: Finding 2nd smallest number in range
12-Feb-09

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

Previous Posts In This Thread:

Finding 2nd smallest number in range
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?

Re: Finding 2nd smallest number in range
=SMALL(IF(A1:A10<>0,A1:A10),2
array formula so enter with Shift+Ctrl+Ente
best wishe
-
Bernard V Liengm
Microsoft Excel MV
http://people.stfx.ca/bliengm
remove caps from email

Re: Finding 2nd smallest number in range
Did you try this : =SMALL(A1:A100,2) this formula ignores text, blank cells an
"0
HT
John

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

:

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

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

:

Hi RachelSorry don't know it, wait and see from the others, then will both
Hi Rache
Sorry do not know it, wait and see from the others, then will both know it
Sorr
John

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

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 rn
If num <> 0 And num < Small The
Small = nu
End I
Nex
Debug.Print "Small " & Smal
mytest = WorksheetFunction.Max(rng
For Each num In rn
If IsNumeric(num) And num <> 0 And num <> Small The
If num < mytest The
mytest = nu
End I
End I
Nex
nextsmall = mytes
End Functio

best wishe
--
Bernard V Liengm
Microsoft Excel MV
http://people.stfx.ca/bliengm
remove caps from emai


Re: Finding 2nd smallest number in range
Thanks Bernard, I will give it a whirl..

:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Custom Favorites Web Site with MongoDb and NoRM
http://www.eggheadcafe.com/tutorial...favorites-web-site-with-mongodb-and-norm.aspx

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP