Ranking a dynamic range containing blanks

Hi guys,

I have a range of cells from A1 to A65 which is populated with formulas to fill with numbers depending on what option is selected from the list box.

The values which populate need to be ranked in column B. (highest value - 1, second higest -2 etc)

Not all cells are populated, some will return a "" (blank) value, these are scattered throughout the range.

I need ONLY the populated cells to be ranked.( the cells which return a "" value should be ignored) These blank cells are scattered throughout the range.

I need to rank them with NO DUPLICATE ranks (eg, if 2 values are the same, the rank must still incriment and be different for each entry)

I am at a brink wall so as always your help will be much appreciated!!

Thanks!

This might help:

Rank Help Files

(the file you want is Rank-TieBreaking.xls)


==========
Some various snippets:

=RANK(B1,B$1:B$20)+COUNTIF(B$1:B1,B1)-1
'positional solution, higher in list wins

=RANK($B2,$B$2:$B$15,1)+SUMPRODUCT(($B$2:$B$15=$B2)($C$2:$C$15>$C2))
'tie breaker values used in additional column
http://www.mrexcel.com/forum/showthread.php?t=428947

'RANK with 1st,2nd,3rd format automatically
=RANK(I2,$I$2:$I$11)&MID("thstndrdth",MIN(9,2*RIGHT(RANK(I2,$I$2:$I$11))*(MOD(RANK(I2,$I$2:$I$11)-11,100)>2)+1),2)
http://www.officekb.com/Uwe/Forum.as...-2nd-positions

=RANK(I2,$I$2:$I$11)&MID("thstndrdth",MIN(9,(RIGHT(RANK(I2,$I$2:$I$11))-1)*2+3),2)

Well, you do have some ties, all the cells with a value of zero, for instance.

Anyway, something like this in D3, then copied down:

=IF(A3="","",RANK(B3,B:B))

or

=IF(OR(A3="",B3=0),"",RANK(B3,B:B))

I gave you snippets above to deal with duplicates. Try the first one edited for your formula.

You can do it.