Rank - with blanks

[Howard Brazee]

I have a spread sheet, and I am trying to do the following function:

=IF(F5>0,RANK(F5,$F$4:$F$102)," ")

I have 5 numbers and 95 blanks in that column, and the numbers rank 5-1.

But they are in the wrong order, so I changed to:

=IF(F5>0,RANK(F5,$F$4:$F$102,1)," ")

Now, the rank is from 95-100

What is the best way to rank the non-blank cells from lowest to highest?

==========================

What I really want to do is some calculation involving the 2nd lowest, the 3rd
lowest, and the number of numbers found.

 

[Paul]

I have a spread sheet, and I am trying to do the following function:

=IF(F5>0,RANK(F5,$F$4:$F$102)," ")

I have 5 numbers and 95 blanks in that column, and the numbers rank 5-1.

But they are in the wrong order, so I changed to:

=IF(F5>0,RANK(F5,$F$4:$F$102,1)," ")

Now, the rank is from 95-100

What is the best way to rank the non-blank cells from lowest to highest?

Are you sure that the 95 cells are truly blank (as your second formula gives
me 1-5)? You can use =ISBLANK(F4) to see if F4 is truly blank.
Alternatively, try your formula in a new workbook so that you can be sure
the cells (except for the 5 numbers and formula you type in) are blank.
(Also see my COUNT formula below).

What I really want to do is some calculation involving the 2nd lowest, the 3rd
lowest, and the number of numbers found.

=COUNT($F$4:$F$102)
will tell you how many numbers there are in the range.

=SMALL($F$4:$F$102,2)
will give you the second smallest, and
=SMALL($F$4:$F$102,3)
will give you the third smallest.