• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to find the 2nd Lowest in a range of cell

Dear all,

I have values in a2,b2,c2,d2,e2,f2,g2,h2,i2,j2,k2 I have to find the lowest non zero values in ( a2,c2,f2,i2,k2)

Ex: 2,5,6,8,9,0,45,78,58,98,88, lower of (2,6,0,58,88) is 2.

Is there any formula is there please do the needful
 

Hui

Excel Ninja
Staff member
Vmohan

2 steps

1. Make a named range like

SmallRng =Sheet1!$A$2,Sheet1!$C$2,Sheet1!$F$2,Sheet1!$I$2,Sheet1!$K$2

2. Enter a formula

=Small(SmallRng,2)
 
Hi ,

Suppose in the range "0" is not there then it has to pick the lowest of the range,

If in a range "0" is there then it has to pick lower value.


Ex

If in a Range (2,6,0,58,88)result=2

If in a Range ( 2,1,5,6,8) result=1 .

I need a formula which satisfies both the condition.
 

Hui

Excel Ninja
Staff member
Change the formula to

=SMALL(SmallRng,IF(MIN(SmallRng)=0,2,1))
 

xld

Member
Try this array formula, which works however many zeroes that you have


=MIN(IF(rng<>0,rng))
 

Hui

Excel Ninja
Staff member
@Xld

Your solution works if the range is continuous

but falls over where the range is discontinuous like Vmohan's
 

Hui

Excel Ninja
Staff member
@XLD

Your solution works where the ranges is continuous, but falls over where, like in Vmohans case above, the range is discontinuous eg: (a2,c2,f2,i2,k2).
 
Top