Order values

[dungeon1976]

Hi,

I don't know if this can be done in without VBA.

I want to rank (descending) values in a column. That could be easily done with the RANK formula, however if there are ties the value after the rank tie will take into consideration the number of tied values and add it to to get the order of the next value.

Example:
Raw values
A 5
B 3
C 5
D 4
F 3
G 0

Descending ranking using the RANK formula
1- A 5
C 5
3- D 4
4- B 3
F 3
6- G 0

What I wanted to look like would be:
1- A 5
C 5
2- D 4
3- B 3
F 3
4- G 0

Is there any way to get this?

Any help would be appreciated...

Thanks.

 

[piiteer]

Do you need something like this?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Raw data[/TD]
[TD][/TD]
[TD](help)[/TD]
[TD]Sorted data[/TD]
[TD][/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]A[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]D[/TD]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]B[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]F[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]G[/TD]
[TD]0[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
(help) column:

=COUNTIF($B$2:B2, ">="&B2) + COUNTIF(B3:$B$7, ">"&B2)

Sorted data (letters):

=INDEX($A$2:$A$7,MATCH(ROW() - ROW($D$2) + 1, $C$2:$C$7, 0))

Sorted data (values):

=INDEX($B$2:$B$7,MATCH(ROW() - ROW($D$2) + 1, $C$2:$C$7, 0))

Rank column:
First field is set to 1, the next one is:

=IF(E3=E2, F2, F2+1)

Let me know if that's what you want. If you have any questions feel free to ask.

 

[dungeon1976]

Thank you piiteer, that is exactly what I need...

Thank you very much for your help.