| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning 2nd smallest value in a range
I'm using the SMALL formula to return 2nd smallest value in a range of
cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
Ah, I see you have posted this several times. You have a suggestion at
one of your other threads. Pete On Feb 12, 1:47*pm, Rachel7 wrote: *I'm using the SMALL formula to return 2nd smallest value in a range of cells. *Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
See reply below
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rachel7" wrote in message ... I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
Hi Rachel,
You can try following formula, =SMALL(A1:A42,1+FREQUENCY(A1:A42,MIN(A1:A42))) H S Shastri ================================================== === "Rachel7" wrote: I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
That works brilliantly...
Thanks.!! "HARSHAWARDHAN. S .SHASTRI" wrote: Hi Rachel, You can try following formula, =SMALL(A1:A42,1+FREQUENCY(A1:A42,MIN(A1:A42))) H S Shastri ================================================== === "Rachel7" wrote: I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
Hats off for a great formula!
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Hi Rachel, You can try following formula, =SMALL(A1:A42,1+FREQUENCY(A1:A42,MIN(A1:A42))) H S Shastri ================================================== === "Rachel7" wrote: I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
However it does not ignore zeros
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Hi Rachel, You can try following formula, =SMALL(A1:A42,1+FREQUENCY(A1:A42,MIN(A1:A42))) H S Shastri ================================================== === "Rachel7" wrote: I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
I can work around the zero's & ensure they're not in the array. Mainly it's
the multiple entries that cause a problem. I need to retrieve 1st,2nd,3rd,4th best values & I wouldn't need to see the duplicate values. Thanks everyone... "Bernard Liengme" wrote: However it does not ignore zeros -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Hi Rachel, You can try following formula, =SMALL(A1:A42,1+FREQUENCY(A1:A42,MIN(A1:A42))) H S Shastri ================================================== === "Rachel7" wrote: I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
Hi Rachel
Pl note that this formula will give you only 2nd small value.This can not give you 3 rd and 4th small value. H S Shastri ================================================== "Rachel7" wrote: I can work around the zero's & ensure they're not in the array. Mainly it's the multiple entries that cause a problem. I need to retrieve 1st,2nd,3rd,4th best values & I wouldn't need to see the duplicate values. Thanks everyone... "Bernard Liengme" wrote: However it does not ignore zeros -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "HARSHAWARDHAN. S .SHASTRI" wrote in message ... Hi Rachel, You can try following formula, =SMALL(A1:A42,1+FREQUENCY(A1:A42,MIN(A1:A42))) H S Shastri ================================================== === "Rachel7" wrote: I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Returning 2nd smallest value in a range
Hi Rachel,
Suppose the range of numbers is as follows in range D5:D11 1 1 0 5 6 1 In cell E5, enter the following formula =IF(OR(COUNTIF($D$5:$D5,D5)<1,COUNTBLANK(D5)=1),1 000,1) and copy down till E11 Now you can array enter (Ctrl+Shift+Enter) the following formula =SMALL(IF(($E$5:$E$11=1),D5:D11),2) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Rachel7" wrote in message ... I'm using the SMALL formula to return 2nd smallest value in a range of cells. Is there a way of the formula excluding empty cells, zero value cells & cells with same values in? Thanks in advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding 2nd smallest number in range | Excel Worksheet Functions | |||
| Returning the smallest value above a certain criteria | Excel Worksheet Functions | |||
| Find X number of smallest values in a range | Excel Worksheet Functions | |||
| Formatting smallest number in a range? | Excel Worksheet Functions | |||
| Getting the 2nd largest or smallest valuesin a range | Excel Discussion (Misc queries) | |||
Linear Mode
