Rank Formula
 

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"lightninbug" wrote in message
...
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

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.

lightninbug wrote:
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

Thanks!