Rank Function Help Needed

[Ms-Exl-Learner]

Hi All,

I need to use the rank function for the below example

A Column B Column C Column
Total Formula Rank Desired Rank
20 8 1
40 7 1
20 8 1
60 5 2
80 3 3
20 8 3
88 2 4
60 5 5
70 4 6
90 1 7

Here A column Consist the Total and I want to apply the rank function for
the A Column Value when I use this formula =RANK(A2,$A$2:$A$11) in B2 Cell
and drag it upto B11 Cell then I am getting the above results. But I require
the results as per the C Column.

Please guide me how it can be done.

Thanks for reading and waiting for your valuable reply.

 

[Ms-Exl-Learner]

Sorry for the inconvenience, Please ignore my previous example, since I have
mentioned wrong Values in the Desired Rank (C Column).

This is the correct one.

A Column B Column C Column
Total Formula Rank Desired Rank
20 8 7
40 7 6
20 8 7
60 5 5
80 3 3
20 8 7
88 2 2
60 5 5
70 4 4
90 1 1

Thank you,

 

[muddan madhu]

row 6 rank is 8 in column B but in Column C its 3

where row 1&3 rank is 8 in column B but in column C its 1

How to differentiate ??

Any logic behind this ??

 

[Tushar Mehta]

See
Ranking
http://www.tushar-mehta.com/excel/newsgroups/ranking/index.html
in particular the section 'Retain duplicate values but create
continuous ranks, i.e., no breaks in the rank.'

Sorry for the inconvenience, Please ignore my previous example, since I have
mentioned wrong Values in the Desired Rank (C Column).

This is the correct one.

A Column B Column C Column
Total Formula Rank Desired Rank
20 8 7
40 7 6
20 8 7
60 5 5
80 3 3
20 8 7
88 2 2
60 5 5
70 4 4
90 1 1

Thank you,

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins

 

[muddan madhu]

try this

=IF(SUMPRODUCT(--(RANK(A1,$A$1:$A$10,0)={8;7})),RANK(A1,$A$1:$A
$10,0)-1,RANK(A1,$A$1:$A$10,0))

Sorry for the inconvenience, Please ignore my previous example, since I have
mentioned wrong Values in the Desired Rank (C Column).

This is the correct one.

A Column                B Column                C Column
Total           Formula Rank            Desired Rank
20              8               7
40              7               6
20              8               7
60              5               5
80              3               3
20              8               7
88              2               2
60              5               5
70              4               4
90              1               1

Thank you,

--------------------
(Ms-Exl-Learner)
--------------------

Hi All,

I need to use the rank function for the below example

A Column           B Column                C Column
Total              Formula Rank            Desired Rank
20         8               1
40         7               1
20         8               1
60         5               2
80         3               3
20         8               3
88         2               4
60         5               5
70         4               6
90         1               7

Here A column Consist the Total and I want to apply the rank function for
the A Column Value when I use this formula =RANK(A2,$A$2:$A$11) in B2Cell
and drag it upto B11 Cell then I am getting the above results.  But Irequire
the results as per the C Column.

Please guide me how it can be done.

Thanks for reading and waiting for your valuable reply.

 

[Mike H]

Hi,

I can't see the logic in your reversing the rank of 60 & 70 s0 assume that's
a typo, try this

=SUMPRODUCT(--(A1<$A$1:$A$10),1/COUNTIF($A$1:$A$10,$A$1:$A$10&""))+1

Mike

 

[Ms-Exl-Learner]

Thank you it's working fine...

--------------------
(Ms-Exl-Learner)
--------------------

try this

=IF(SUMPRODUCT(--(RANK(A1,$A$1:$A$10,0)={8;7})),RANK(A1,$A$1:$A
$10,0)-1,RANK(A1,$A$1:$A$10,0))

Sorry for the inconvenience, Please ignore my previous example, since I have
mentioned wrong Values in the Desired Rank (C Column).

This is the correct one.

A Column B Column C Column
Total Formula Rank Desired Rank
20 8 7
40 7 6
20 8 7
60 5 5
80 3 3
20 8 7
88 2 2
60 5 5
70 4 4
90 1 1

Thank you,

--------------------
(Ms-Exl-Learner)
--------------------

Hi All,

I need to use the rank function for the below example

A Column B Column C Column
Total Formula Rank Desired Rank
20 8 1
40 7 1
20 8 1
60 5 2
80 3 3
20 8 3
88 2 4
60 5 5
70 4 6
90 1 7

Here A column Consist the Total and I want to apply the rank function for
the A Column Value when I use this formula =RANK(A2,$A$2:$A$11) in B2 Cell
and drag it upto B11 Cell then I am getting the above results. But I require
the results as per the C Column.

Please guide me how it can be done.

Thanks for reading and waiting for your valuable reply.

.

 

[Ms-Exl-Learner]

Many thanks it's working fine Mike Sir!