How to rank or sort this?

[Luthius]

I have one table with the following values

Name	Points
Mary	12
John	16
Joseph	15
Hilary	16
Richard	13

I would like something like this:

Name	Points	Rank
Mary	12	1º
John	16	5º
Joseph	15	3º
Hilary	16	4º
Richard	13	2º

What I mean is that I will rank the values ​​using the data in column B, but if the column B has equal values​​, the formula will rank using as parameter the name in alphabetical order.

Luthius

 

[wigi]

Use an extra (hidden) column, with:

=B2-ROW(B2)/1000

Then, on that range of cells, apply the RANK function. You will be sure to have unique ranks.

 

[Luthius]

I would like just one formula without auxiliar columns.
How do I do it?

 

[fromspenny]

Excel Workbook
	A	B	C
1	Name	Points	Rank
2	Mary	12	5
3	John	16	1
4	Joseph	15	3
5	Hilary	16	2
6	Richard	13	4
Sheet

 

[Sanjeev1976]

Just a small change to fromspenny's formula to suit your requirement :

=RANK(B2,$B$2:$B$6,1)+COUNTIF(B$1:B1,$B2) in Cell C2 and then copy to below cells.

Sanjeev

 

[Luthius]

Great!!!
Now I want a little different.
I want the result listing names as rank like this:

Rank	Name
1º	John
2º	Hilary
3º	Joseph
4º	Richard
5º	Mary

 

[jasonb75]

Great!!!
Now I want a little different.
I want the result listing names as rank like this:

Rank    Name
1º    John
2º    Hilary
3º    Joseph
4º    Richard
5º    Mary

So why didn't you ask that to start with?

Delete the rank formula, you don't need that now (total waste of time and effort for all involved).

Sort your data by Points then by Name.

Rank the first entry using =ROW(A1)

 

[Luthius]

No It isn't waste of time.
I need it with formula. That's the reason I asked.
My first question was solved, Now I want as I wrote using formula.

 

[Sanjeev1976]

Once you get the desired result using the formula, just sort on Column C.

Sanjeev

 

[Luthius]

Ok Thanks...
But now I want this sorting using formula as my example.