Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |