Finding the second lowest number in a list

Arstron · Newbie Joined: 04 Feb 2008 Posts: 2

I am having some troubles getting a the second lowest number in a row of numbers to display in a cell. After searching around on this forum I came up with this, which works, but does not work if there are two 0's in the same row. Here is what I currently have:

=SMALL(D4:M4;COUNTIF(D4:M4;O4)+1)

With out the +1, it simply finds the lowest number, I know there has to be a way to do this, but I just am not smart enough.

David · Posted: Mon Feb 04, 2008 1:46 pm Post subject:

Try this:

In A1: =SMALL(D4:M4;2)
In A2: =COUNTIF($D$4:$M$4;A1)
In A3: =SMALL(D4:M4;A2+1)

David.

Arstron · Newbie Joined: 04 Feb 2008 Posts: 2

I get this for each cell
a1: 0
a2: 2
a3: 6

this is the number sequence: 0 0 28 6

After trying a few things I noticed that on any row that has the same lowest and second lowest numbers, it would ignore the second lowest number and go on to the 3rd highest. This is what I am now using, but it still will not show a 0 if there are two 0's in a sequence

=IF(SMALL(D6:M6;COUNTIF(D6:M6;O6))=O6;SMALL(D6:M6;COUNTIF(D6:M6;O6)+1);(SMALL(D6:M6;COUNTIF(D6:M6;O6)))).

Never mind, I now see that the first one is all that I was needing, it is working great, thanks a lot!

huwg · Super User Joined: 14 Feb 2007 Posts: 890