Ranking

[Thrava]

Hello folks,

I have a list of about 350 sales reps. I like to be able
to rank them automatically based on the numbers that
appear in front of their name in column B.

These numbers are in percent with one decimal point
accuracy.

some of the reps of course have the same percent so they
need to be given the same ranking.

Example
Name percent Rank
Sharif 78.4% 1
Aljandro 66.9% 2
Flora 66.9% 2
Yusef 59% 3
John 48% 4
Anna 48% 4

What formul in the column for ranking can I use to
automate this please?

[Peo Sjoblom]

If there are ties for second place then there is no third place
assume the values are in B1:B6

=RANK(B1,$B$1:$B$6,)


copy down

--


Regards,

Peo Sjoblom


"Thrava" <[email protected]> wrote in message
news:[email protected]...
> Hello folks,
>
> I have a list of about 350 sales reps. I like to be able
> to rank them automatically based on the numbers that
> appear in front of their name in column B.
>
> These numbers are in percent with one decimal point
> accuracy.
>
> some of the reps of course have the same percent so they
> need to be given the same ranking.
>
> Example
> Name percent Rank
> Sharif 78.4% 1
> Aljandro 66.9% 2
> Flora 66.9% 2
> Yusef 59% 3
> John 48% 4
> Anna 48% 4
>
> What formul in the column for ranking can I use to
> automate this please?
>

[Thrava]

Peo,
thank you for your help on this.
What do you mean there is no third place?

>-----Original Message-----
>If there are ties for second place then there is no third
place
>assume the values are in B1:B6
>
>=RANK(B1,$B$1:$B$6,)
>
>
>copy down
>
>--
>
>
>Regards,
>
>Peo Sjoblom
>
>
>"Thrava" <[email protected]> wrote in
message
>news:[email protected]...
>> Hello folks,
>>
>> I have a list of about 350 sales reps. I like to be
able
>> to rank them automatically based on the numbers that
>> appear in front of their name in column B.
>>
>> These numbers are in percent with one decimal point
>> accuracy.
>>
>> some of the reps of course have the same percent so they
>> need to be given the same ranking.
>>
>> Example
>> Name percent Rank
>> Sharif 78.4% 1
>> Aljandro 66.9% 2
>> Flora 66.9% 2
>> Yusef 59% 3
>> John 48% 4
>> Anna 48% 4
>>
>> What formul in the column for ranking can I use to
>> automate this please?
>>
>
>
>.
>

[Ken Russell]

He means if you have for example four contestants. A scores 90, B & C score
80 and D scores 70.

The ranking is;
First: A
equal Second: B & C
Fourth: D

Similarly, if three people tied for Second, then the next rank would be
Fifth.
--
Ken Russell

[email protected]
Remove yourhat to reply by e-mail
..

"Thrava" <[email protected]> wrote in message
news:[email protected]...
> Peo,
> thank you for your help on this.
> What do you mean there is no third place?
>
>>-----Original Message-----
>>If there are ties for second place then there is no third
> place
>>assume the values are in B1:B6
>>
>>=RANK(B1,$B$1:$B$6,)
>>
>>
>>copy down
>>
>>--
>>
>>
>>Regards,
>>
>>Peo Sjoblom
>>
>>
>>"Thrava" <[email protected]> wrote in
> message
>>news:[email protected]...
>>> Hello folks,
>>>
>>> I have a list of about 350 sales reps. I like to be
> able
>>> to rank them automatically based on the numbers that
>>> appear in front of their name in column B.
>>>
>>> These numbers are in percent with one decimal point
>>> accuracy.
>>>
>>> some of the reps of course have the same percent so they
>>> need to be given the same ranking.
>>>
>>> Example
>>> Name percent Rank
>>> Sharif 78.4% 1
>>> Aljandro 66.9% 2
>>> Flora 66.9% 2
>>> Yusef 59% 3
>>> John 48% 4
>>> Anna 48% 4
>>>
>>> What formul in the column for ranking can I use to
>>> automate this please?
>>>
>>
>>
>>.
>>

[Jerry W. Lewis]

Try the formula and see. RANK() returns 1,2,2,4,5,5 as the ranks for
your data.

Jerry

Thrava wrote:

> Peo,
> thank you for your help on this.
> What do you mean there is no third place?
>
>
>>-----Original Message-----
>>If there are ties for second place then there is no third
>>
> place
>
>>assume the values are in B1:B6
>>
>>=RANK(B1,$B$1:$B$6,)
>>
>>
>>copy down
>>
>>--
>>
>>
>>Regards,
>>
>>Peo Sjoblom
>>
>>
>>"Thrava" <[email protected]> wrote in
>>
> message
>
>>news:[email protected]...
>>
>>>Hello folks,
>>>
>>>I have a list of about 350 sales reps. I like to be
>>>
> able
>
>>>to rank them automatically based on the numbers that
>>>appear in front of their name in column B.
>>>
>>>These numbers are in percent with one decimal point
>>>accuracy.
>>>
>>>some of the reps of course have the same percent so they
>>>need to be given the same ranking.
>>>
>>>Example
>>>Name percent Rank
>>>Sharif 78.4% 1
>>>Aljandro 66.9% 2
>>>Flora 66.9% 2
>>>Yusef 59% 3
>>>John 48% 4
>>>Anna 48% 4
>>>
>>>What formul in the column for ranking can I use to
>>>automate this please?
>>>
>>>
>>
>>.
>>
>>

[RagDyeR]

Think of it in terms of prize money.

2 people tied for second equally share the combined second and third place
reward, leaving the fourth place prize money to be awarded to the next
(4th) place holder.

It wouldn't be fair if they had to split second place money, where the next
place holder would receive a larger (3rd) individual, undivided prize, even
though he came in with a lesser score then the other two.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Thrava" <[email protected]> wrote in message
news:[email protected]...
Peo,
thank you for your help on this.
What do you mean there is no third place?

>-----Original Message-----
>If there are ties for second place then there is no third
place
>assume the values are in B1:B6
>
>=RANK(B1,$B$1:$B$6,)
>
>
>copy down
>
>--
>
>
>Regards,
>
>Peo Sjoblom
>
>
>"Thrava" <[email protected]> wrote in
message
>news:[email protected]...
>> Hello folks,
>>
>> I have a list of about 350 sales reps. I like to be
able
>> to rank them automatically based on the numbers that
>> appear in front of their name in column B.
>>
>> These numbers are in percent with one decimal point
>> accuracy.
>>
>> some of the reps of course have the same percent so they
>> need to be given the same ranking.
>>
>> Example
>> Name percent Rank
>> Sharif 78.4% 1
>> Aljandro 66.9% 2
>> Flora 66.9% 2
>> Yusef 59% 3
>> John 48% 4
>> Anna 48% 4
>>
>> What formul in the column for ranking can I use to
>> automate this please?
>>
>
>
>.
>

[Bob Phillips]

You can force it to have unique ranks with

=RANK(B1,$B$1:$B$6)+COUNTIF($B$1:B1,B1)-1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:42116A57.2000406@no_e-mail.com...
> Try the formula and see. RANK() returns 1,2,2,4,5,5 as the ranks for
> your data.
>
> Jerry
>
> Thrava wrote:
>
> > Peo,
> > thank you for your help on this.
> > What do you mean there is no third place?
> >
> >
> >>-----Original Message-----
> >>If there are ties for second place then there is no third
> >>
> > place
> >
> >>assume the values are in B1:B6
> >>
> >>=RANK(B1,$B$1:$B$6,)
> >>
> >>
> >>copy down
> >>
> >>--
> >>
> >>
> >>Regards,
> >>
> >>Peo Sjoblom
> >>
> >>
> >>"Thrava" <[email protected]> wrote in
> >>
> > message
> >
> >>news:[email protected]...
> >>
> >>>Hello folks,
> >>>
> >>>I have a list of about 350 sales reps. I like to be
> >>>
> > able
> >
> >>>to rank them automatically based on the numbers that
> >>>appear in front of their name in column B.
> >>>
> >>>These numbers are in percent with one decimal point
> >>>accuracy.
> >>>
> >>>some of the reps of course have the same percent so they
> >>>need to be given the same ranking.
> >>>
> >>>Example
> >>>Name percent Rank
> >>>Sharif 78.4% 1
> >>>Aljandro 66.9% 2
> >>>Flora 66.9% 2
> >>>Yusef 59% 3
> >>>John 48% 4
> >>>Anna 48% 4
> >>>
> >>>What formul in the column for ranking can I use to
> >>>automate this please?
> >>>
> >>>
> >>
> >>.
> >>
> >>
>