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

#### vmohan1978

##### Member
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

##### Active Member
=min() or =small(range,1)

#### 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)

#### vmohan1978

##### Member
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))

#### vmohan1978

##### Member
Dear Hui,

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

#### xld

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

=MIN(IF(rng&#60;&#62;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).