| Home |
| Search |
| Today's Posts |
|
#5
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? |
| 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) | |||
Threaded Mode
