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

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

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

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

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

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

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

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

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

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