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,

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



"Ms-Exl-Learner" wrote:

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)
--------------------

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

On Sun, 25 Oct 2009 08:24:01 -0700, Ms-Exl-Learner
wrote:

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)
--------------------



"Ms-Exl-Learner" wrote:

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)
--------------------
Regards,

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

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))

On Oct 25, 8:24*pm, Ms-Exl-Learner wrote:
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)
--------------------

"Ms-Exl-Learner" wrote:
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)
--------------------

Thank you it's working fine...

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



"muddan madhu" wrote:

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))

On Oct 25, 8:24 pm, Ms-Exl-Learner wrote:
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)
--------------------

"Ms-Exl-Learner" wrote:
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)
--------------------

.

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 ??


On Oct 25, 8:12*pm, Ms-Exl-Learner wrote:
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)
--------------------

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

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!

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



"Mike H" wrote:

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

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)
--------------------