| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
=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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Finding 2nd smallest number in range
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding the number of times a word is used in a range of cells | Excel Worksheet Functions | |||
| Finding Minimum but if same number repeats in the range, then find | Excel Discussion (Misc queries) | |||
| Find X number of smallest values in a range | Excel Worksheet Functions | |||
| Formatting smallest number in a range? | Excel Worksheet Functions | |||
| Finding Smallest Value | Excel Discussion (Misc queries) | |||
Linear Mode
