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

[vmohan1978]

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

 

[dan_l]

=min() or =small(range,1)

 

[Hui]

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)

 

[vmohan1978]

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]

Change the formula to

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

 

[vmohan1978]

Dear Hui,

Thank you very very much, sparing time to solve the problem. It saved lot of time of mine.

 

[xld]

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


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

 

[Hui]

@Xld

Your solution works if the range is continuous

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

 

[Hui]

@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).