Rank Formula

[lightninbug]

I searched for all posts regarding rank formulas and did not see exactly what
I was looking for. Here is what I need to do - Rank by dollar values, some of
these values are zero and do not need to be included in the ranking or can be
ranked zero. Also, if there are duplicate values they need to be given
separate consecutive ranking. Got any ideas/ Thanks

 

[Max]

Perhaps you'd like to try this past gem by Daniel M at:
http://tinyurl.com/3gmdv

 

[Aladin Akyurek]

A different formula system with a recent use: http://tinyurl.com/52pxp

Let A1:A11 house the following sample:

{"DATA";0;0;0.225;0.225;1;1;2.25;0;4.8;-6.7}

where A1: DATA, A2: 0, A3: 0, A4: 0.225, etc.

In B1 enter the label: I-RANK

In B2 enter & copy down:

=IF(N(A2),IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",RANK(A2,$A$2:$A$11)),"")

In C1 enter the label: F-RANK

In C2 enter & copy down:

=IF((A2=0)*(B2=""),"",IF(N(B2),RANK(B2,$B$2:$B$11,1),VLOOKUP(A2,$A$1:C1,3,0)))

Note that I-RANK stands for Intermediate Rank and F-RANK for Final Rank.

The results in C2:C11 will be :

{"";"";4;4;3;3;2;"";1;5}

corresponding to the values of interest in A2:A11.

 

[Tina]

Thanks!